Level 8 · Boss: two messy exports, one clean table

This boss level is the whole chapter in one query: two raw files in, one clean table out, every step recorded so the next file costs one click. As in the rest of the chapter, you work in the practice editor, not Power Query itself; each step’s starter carries the steps you’ve already written, the way Applied Steps grow in Power BI.

The files

Both exports have the same five columns: order_item_id, order_date, category, quantity and amount, all text. Marketplace_Wk44 (the Source) covers October 27 to November 2, 25 lines; Marketplace_Wk45 covers November 2 to 9, 59 lines. The category is typed four ways: Lighting, lighting, LIGHTING and Lighting with a trailing space.

Step 1: one table, each line once

Append queries: Marketplace_Wk45
Remove duplicates: order_item_id

In Power BI: select the first query, Home → Append Queries (or Append Queries as New to leave both untouched), pick the second. Append matches columns by name; a column only one file has is filled with null in the other’s rows. For a folder of weekly files, Get data → Folder followed by Combine & Transform does the appending for you on every refresh.

Then select order_item_id and Home → Remove Rows → Remove Duplicates. Power Query keeps the first row of each order_item_id. Choosing the key column matters: removing duplicates across all columns would keep both copies of a line the marketplace had corrected between files.

Step 2: types and text

Change type: order_item_id, quantity to whole number
Change type: amount to decimal
Change type: order_date to date
Trim: category
Capitalize each word: category

In Power BI, select columns and use Transform → Data Type, then Transform → Format → Trim and Capitalize Each Word. Look at View → Column quality after the type changes: 0% errors in every column means every value converted. If amount had used a comma as decimal separator, you’d change it Using Locale instead of replacing characters by hand.

Step 3: the summary

Group by: category | revenue = sum of amount | units = sum of quantity
Sort rows: revenue descending

Home → Group By → Advanced, two aggregations (Sum of amount, Sum of quantity), then sort revenue descending.

Load it

Home → Close & Apply runs the query and loads the result into the model; from then on, Home → Refresh in Report view replays every step on the current files. Right-click the two weekly queries → untick Enable load, so only the clean table reaches the model and the raw files stay as inputs.

What it shows

Lighting leads the fortnight with $5,148 from 32 units, almost twice Furniture’s $2,732, which took only 8 units: marketplace furniture orders are rare but large. Decor sold the most units, 49, for $1,541. Revenue and units tell different stories, so Laura gets both side by side.

Common mistakes

• Appending after cleaning only the first file: the second file’s text never gets fixed. Append first, clean once.
• Removing duplicates before appending: each file has no duplicates on its own; the overlap only appears once they’re stacked.
• Grouping on the raw category: lighting and LIGHTING stay separate rows.