Every number in the report starts in one file. Before the model could be built, that file had to be designed for it: one table per sheet, and a sheet that explains the rest.
with Elizabeth Cecilia. Tap any underlined word for a quick definition.
Have the report open. The lessons point at real pages and tiles. The .pbix opens in Power BI Desktop; the PDF works on any device, but things like View as and the i buttons need Desktop.
source.xlsx has 33 sheets. Each holds one Excel table, and the table's name is the sheet's name without sheet_. Together they form a : s in the middle, dimensions around them. reads them into the model.
Download source.xlsxGitHub can't show a workbook in the browser, so this downloads it. Open it in Excel.
Rule 3 of the brief says no numbers of your own: no typed-in values, no tables that restate the data.
Predict first: A new tile needs a figure that isn't in the workbook yet. What does the brief allow?
01
A sheet that explains the rest
The first sheet, sheet_ReadMe, has two columns: Item and Detail. Each row records one decision about the data, so nobody has to guess. Three of its rows:
Percent scale Columns ending in Pct hold whole numbers (79 means 79%). Measures that read them stay on that scale and take the format string 0\%. Measures that calculate a ratio return a fraction and take 0.0%.
Data status Every figure is illustrative and shaped to look plausible. None is a published statistic.
Lithium, two bases Production counts lithium in kt of lithium carbonate equivalent. Material intensity counts contained lithium metal. 1 kt of lithium metal = 5.323 kt LCE.
Predict first: The column ImportReliancePct holds 79 for cobalt. What does the card show?
02
Everything at one grain
Some source figures only exist for a whole group of commodities, monthly trade in euros for example. The model needs them per commodity. The ReadMe records how that was solved:
Trade allocation Group-level monthly imports and exports are allocated to commodities in proportion to import volume, so the fact sits at commodity grain. Sums by group reproduce the source.
The of a table is what one row stands for. Mixing grains in one table is how totals go wrong.
Predict first: Why split group totals down to commodities, rather than keep them per group?
03
Headline figures are not stored
The cobalt card says 290 kt. You won't find a cell with 290 in it labelled "headline". The ReadMe explains:
One number per measure Headline cards are not stored. Mine production 2025 is the last row of FactCommodityAnnual; the price card is the last row of FactPriceAnnual; growth is calculated from that series.
Check it in the report: on Commodity: world with Cobalt selected, the Mine production card says 290 kt. Now look at the last bar for 2025 in Production and price, ten years.
Predict first: Why read the card from the same yearly series as the chart, instead of storing its value separately?
04
Illustrative, and labelled as such
The data is made up to look plausible, and the report says so: every page has the same footer, "Dashboard figures are illustrative, not real market data." Rule 8 names the one exception: the minimum recycled-content shares in the EU Battery Regulation, which are real.
Check it in the report: scroll to the bottom of any page: the footer says the figures are illustrative.
Predict first: Which figures in the kit are real?
05
A mistake put in on purpose
One table is a copy of the monthly trade table with one deliberate break: nickel, March 2025, supplied a thousand times too large, a tonne and kilotonne mix-up. The ReadMe records it, and the model spec says do not fix it.
The Fabric migration page compares the two tables row by row and finds it: 143 of 144 rows match. How that check works is in The move to Fabric.
Check it in the report: open Fabric migration. Rows compared says 143 / 144 rows match, and Where to look first names Nickel 2025-03.
Predict first: Why put a known error into the data?
06
The foundations
The foundations are in. Next lesson: the three scripts that turned the specs and this workbook into the report's files.
One home for every number. One grain per table. Write every data decision down where the next person will look.
And if you want to know a check works, give it something to find.
Terms in this lesson
Earn 5 XP for every term you know.
Get the project files
Everything is free on GitHub. Open the report next to the lessons and check each step for yourself.
The whole project
The Power BI project, the seed project, the specs and the tools: the whole kit. On the GitHub page, click the green Code button, then Download ZIP, and unzip it anywhere.
The Critical Raw Materials Intelligence report as a single .pbix file to open in Power BI Desktop. Open the latest release under Releases, then Assets, on the right of the GitHub page.