Power QueryExercises

Store report with subtotals

A printed-style sales report: region shown once per block, with subtotal and total rows.

What's messy

Columns

ColumnTypeNote
RegiontextFill down
StoretextIncludes Subtotal/Grand Total
Saleswhole number

Try these

How to approach it

Use Fill Down on Region so every store row carries its region, since the label is printed only on the first row of each block. Then remove the Subtotal and Grand Total rows: they are totals of rows already in the table and would double the figures if left in. Filter on those exact labels in the Store column rather than removing rows by position, so the query still works when a region gains a store. Check the result by summing Sales and comparing it with the Grand Total in the original report.

Preview (first 8 rows)

RegionStoreSales
NorthHarbour St 1837
nullStation Rd 26668
nullSubtotal7505
SouthHarbour St 41167
nullMill Lane 55214
nullHarbour St 6820
nullSubtotal7201
EastMill Lane 85567

All data is fictional. CSV cells that could be read as spreadsheet formulas are prefixed with an apostrophe.