FormulaCamp
Completed
9. Power Query · The Query Editor · 20 min

4. Power Query: Unpivot and Totals

Lesson 4 of 8
Task 1 of 3
  1. Create a query from "Sales" and unpivot all the columns except "Product".

Lesson theory

Reports often arrive "wide", with months in columns. PivotTables and charts need a "long" layout: one row per fact (product, month, value).

Unpivot Other Columns: select the columns that stay put (Product) — all the others turn into pairs of Attribute (the old column name, that is, the month) and Value. Power Query skips empty cells.

If someone adds April to the report, it flows into the long table by itself after "Refresh". Formulas can't do that.

In the trainer, the Power Query editor sits under the sheet. Pick a source, add steps with the "+ Step" button, and check the result after each step (click a step in the "Applied Steps" list). The M formula of the step is shown above the preview. Remove a wrong step with "Delete last step".

Practice is part of Pro

The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.