Project: turn a printed report into data
From: Tomás Ortega, finance analyst, Bramble Coffee Roasters (a fictional company)
About: The regional sales file
Each month the regional team sends a sales file built for printing: a title on top, a total at the bottom, each region written once above its stores, and one column per month. One store wrote "n/a" for a month it couldn't close.
I need it as a proper table, one row per store and month, with sales as numbers, and a total per region. And I want the same query to work on next month's file.
This project walks a print-style report all the way to analysis-ready data. It uses almost every Advanced skill: removing rows, promoting headers, filling down, unpivoting, fixing a type error and grouping.
The order matters. Remove the title before promoting the headers. Fill Region down before you unpivot, or the blanks multiply. Change the type after the unpivot, so there's one Sales column to fix, not three month columns.
= Table.UnpivotOtherColumns(#"Filled Down", {"Region", "Store"}, "Attribute", "Value")
Keep two identifying columns, unpivot the months.