Lesson 01 of 7 · Bronze, silver, gold
Raw in. Clean through. Model out.
Every table in this model is refined three times before a report sees it, a bit like ore. Let's follow one row through.
1
Three passes, one direction
The data starts in one Excel workbook. Power Query reads it in three groups of queries, and each group reads only from the one before it. The names come from the lakehouse "medallion" pattern, which is useful later: if the model moves to Microsoft Fabric, each group becomes a set of lakehouse tables and the model on top barely changes.
BBronze
The sheet, exactly as it arrived. No fixes.
Group: Bronze — raw
SSilver
Cleaned: types set, spaces trimmed, names made consistent. One cleaning query per table.
Group: Silver — cleansed
GGold
Shaped for the model: facts and dimensions, keys, sort-order columns.
Group: Gold — model
2
Follow one row
One row in the sheet has the country typed as “ Chile ”, with spaces. Which layer should remove them?
Answer the question above to unlock this.
Layers visited: 1 of 3
An illustrative row. The query groups and their order are the real ones.
3
Which layer owns the job?
0 / 6
Pick a layer for each job.
Read the workbook sheet exactly as it is
Set column types and trim stray spaces
Remove duplicate rows
Rename columns to one naming convention
Swap country names for keys that join to a dimension
Add a sort-order column so stages list in process order
The ruleEach layer reads only from the one before it. Fix a problem in the earliest layer that owns it, and never in the source workbook.