Level 7 · Merge in the category, then group the returns

Angela’s export knows which product came back but not what kind of product it is. Two steps fix that: a merge that looks the category up, and a group by that turns 44 rows into one row per category.

Merge: look up the category

Merge queries: Products on product_id, expand category

In Power BI: select the returns query, Home → Merge Queries, choose Products as the second table, click product_id in both previews, keep Left Outer, OK. Power Query adds a column holding, for each return, the matching rows of Products. Click the expand icon (two arrows) on its header, tick only category, and untick Use original column name as prefix so the column is called category and not Products.category. Two steps are recorded:

Table.NestedJoin(Source, {"product_id"}, Products, {"product_id"}, "Products", JoinKind.LeftOuter)
Table.ExpandTableColumn(#"Merged Queries", "Products", {"category"}, {"category"})

Check the match count at the bottom of the Merge dialog: all 44 returns should find their product. A null category after expanding means a product missing from the list.

Group by: one row per category

Group by: category | returns = count rows | refunded = sum of refund_amount

In Power BI: Home → Group By (or Transform → Group By), choose Advanced to add more than one aggregation. Group by category; first aggregation: new column name returns, operation Count Rows; Add aggregation: refunded, operation Sum, column refund_amount. Other operations: Average, Median, Min, Max, Count Distinct Rows, and All Rows, which keeps the grouped rows as a nested table. Columns not grouped or aggregated are dropped, which is what a summary is.

Table.Group(#"Expanded Products", {"category"},
    {{"returns", each Table.RowCount(_), Int64.Type},
     {"refunded", each List.Sum([refund_amount]), type number}})

Sort, biggest first

Sort rows: refunded descending

Click the arrow on the refunded header → Sort Descending. In a report you’d usually sort in the visual instead; here the table itself goes to Angela.

What it shows

category	returns	refunded
Outdoor	4	2,193.52
Lighting	6	980.95
Kitchen	10	910.74
Textiles	14	684.86
Decor	9	683.89
Furniture	1	179.00

Textiles had the most returns, Outdoor the most money back from just four items. Bring both columns to the meeting: the count is the support team’s workload, the refund total is the cost.

Group in Power Query, or in the model?

Grouping in Power Query loads only the summary: small and fast, but nobody can drill into a single return. For a report you’d usually load all 44 rows and let a measure do the totals, so slicers on reason or date still work. Group in Power Query when the detail is never needed.

Common mistakes

• Expanding every Products column. name and a duplicate product_id clutter the table.
• Sum of return_id. A sum of IDs is a meaningless number; count rows instead.
• Forgetting to rename. Count and Sum as column names mean nothing next month.