Create a query from "Sales" and pivot the column: months become columns, Amount provides the values (sum).
Lesson theory
Pivot Column (Transform › Pivot Column) is the opposite of Unpivot: the values of one column become the headers of new columns, and another column is aggregated into the cells. A long "City — Month — Amount" table turns into a report with cities in rows and months in columns.
- if a city–month pair has several rows, the values are added up (the "Sum" operation);
- if there is no such pair, the cell holds null, which is an empty cell once loaded into Excel;
- the step formula:
Table.Pivot(…, List.Distinct(…[Month]), "Month", "Amount", List.Sum).
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.