Monthly units (wide)
A planning sheet with one column per month, from a fictional café chain.
What's messy
- Months as columns: needs unpivoting
- Next month's column will be added later
Columns
| Column | Type | Note |
|---|---|---|
| Item | text | |
| Jan … Jun | whole number | One column per month |
Try these
- Unpivot Other Columns into Month and Units
- Total units per item
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)
| Item | Jan | Feb | Mar | Apr | May | Jun |
|---|---|---|---|---|---|---|
| Coffee Beans | 354 | 83 | 53 | 340 | 274 | 218 |
| Oat Milk | 230 | 170 | 261 | 323 | 130 | 24 |
| Paper Cup | 409 | 354 | 352 | 23 | 186 | 269 |
| Lid | 226 | 226 | 219 | 150 | 394 | 53 |
| Sugar Sachet | 160 | 419 | 185 | 416 | 194 | 260 |
| Napkin | 127 | 96 | 195 | 151 | 162 | 23 |
All data is fictional. CSV cells that could be read as spreadsheet formulas are prefixed with an apostrophe.