Level 7 · Boss: from a messy export to revenue by category

One export, three steps, every two weeks

This boss level is the whole chapter in one query. Thomas wants revenue and units by category, biggest first, every two weeks, without anyone redoing the work. That is what a query is for: the steps you add today replay on every future file.

As in the rest of the chapter, you are in the practice editor, not Power Query itself. Each step’s starter carries the steps you already wrote, the way Applied Steps keep growing in Excel.

The source has 48 lines from September 1 to 15, 2025: order_id, order_date, product_id, quantity and unit_price. The export wrote everything as text, and the marketplace hadn’t priced the last two days yet, so 6 lines say pending.

Step 1: types and errors

Change type: quantity to whole number
Change type: unit_price to decimal
Remove errors: unit_price

After the second step, the 6 pending cells show Error: text that can’t become a number. In Excel’s editor the thin bar under the column header turns partly red, and clicking an Error cell shows the reason (“We couldn’t convert to Number”). Home → Remove Rows → Remove Errors drops those rows: 42 remain.

Removing rows is a decision, not housekeeping. Here it is right, because those lines have no price yet and the next report will include them. Say so in the note you send: “Sept 14–15 marketplace lines are not priced yet and are excluded.” When errors come from a data problem instead (a price typed as 12,99), fix the value with Replace Values before changing the type rather than throwing the line away.

In M:

#"Changed Type" = Table.TransformColumnTypes(Source, {{"quantity", Int64.Type}, {"unit_price", type number}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"unit_price"})

Change the second type right after the first and Excel asks whether to replace the current step or add a new one; Replace keeps both in one step, as above. The practice editor keeps one line per column.

Step 2: the category lookup

Merge queries: Products on product_id, expand category

The export only knows product ids. Products holds the name and category of every product that appears in it. A left outer merge on product_id adds the category to each of the 42 lines. All of them match. A null category would mean a new product missing from the list, and its revenue would land in a “null” group in the next step.

Step 3: totals by category

Add column: line_total = [quantity] * [unit_price]
Group by: category | revenue = sum of line_total | units = sum of quantity
Sort rows: revenue descending

In Excel: Home → Group By → Advanced, group by category, then add one aggregation per row: revenue = Sum of line_total, units = Sum of quantity. The M:

Table.Group(#"Added Custom", {"category"},
    {{"revenue", each List.Sum([line_total]), type number},
     {"units", each List.Sum([quantity]), Int64.Type}})

The result is six rows, one per category.

Load it, chart it, refresh it

Home → Close & Load To… gives you the choice:

Load to	Use it when
Table on a new sheet	you want the grouped table itself, for a chart or a formula
PivotTable Report	you’d rather load the 42 lines and summarise them in a PivotTable (skip Group by)
Only Create Connection	another query uses this one (like Products here)

For Thomas, load the grouped table and insert a bar chart (Insert → Charts → Clustered Bar) on revenue: categories are long labels and bars read best sorted, which the query already did. Or load the 42 lines and build a PivotTable with category in Rows and revenue in Values; then add a PivotChart. Both give the same numbers.

In two weeks, save the new CSV over the old one and press Data → Refresh All. The query re-imports, re-types, removes the new pending lines, merges and groups; the table and the chart update. If the new file has a new product, refresh Products too (Refresh All does both).

What can you conclude?

Textiles leads with 1,160.83 from 20 units, but Lighting is only 11.90 behind with 11 units: the two are tied for any practical purpose. Furniture makes 717.00 from just 3 units, and Kitchen sells 17 units for 545.83. Revenue and units tell different stories, so show both, and say above the chart what it leaves out (the unpriced lines).

Common mistakes

• Changing both columns to decimal: quantities should be whole numbers, and a decimal type hides data problems like 2.5 units.
• Removing errors across all columns when only unit_price is expected to fail: a new problem elsewhere would silently delete rows.
• Grouping on unit_price instead of line_total: the sum of prices ignores quantities.
• Forgetting the sort: the numbers are right, but the chart reads in a random order.