Power QueryExercises

Monthly units (wide)

A planning sheet with one column per month, from a fictional café chain.

What's messy

Columns

ColumnTypeNote
Itemtext
Jan … Junwhole numberOne column per month

Try these

How to approach it

Select the Item column and use Unpivot Other Columns rather than selecting the six month columns. That way a July column added next month is unpivoted too, without editing the query. Rename the new Attribute and Value columns to Month and Units, and set Units to a whole number. Month is still text such as Jan, so if it needs to sort in calendar order, merge it with a small month-number table or build a date from it. Finish by grouping by Item to total the units, and check that the total matches the sum of the original sheet.

Preview (first 8 rows)

ItemJanFebMarAprMayJun
Coffee Beans3548353340274218
Oat Milk23017026132313024
Paper Cup40935435223186269
Lid22622621915039453
Sugar Sachet160419185416194260
Napkin1279619515116223

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