4.3 Reshaping and combining with Power Query

What you'll be able to do

Combine sources, and reshape reports built for human eyes into tables you can analyse. Join two lists on a key, stack a folder of months, and undo the month-per-column grid that every office produces.

Before you start

Have the messy data workbook and the lookup-and-join workbook open. They give you two tables that want joining, and shapes that want fixing.

The work

1. No documented keyboard shortcut for this — use the menu: Merge is the join. With both tables loaded as queries, go to Home → Merge Queries in the editor and click the key column in each. Then choose the join kind, which is the decision that matters. Left Outer keeps every row of the first table and matches where it can, and it is the usual choice for a report. Inner keeps only the rows that are in both. Left Anti keeps the rows of the first table with no match at all. That is the "who is missing?" report, and quietly one of the most useful joins in office life.
2. Same either way: after a merge, the new column holds tables inside it. Click the expand icon on that column and tick the columns you want brought across. Rows that found no match get null, which is the honest empty.
3. Same either way: read Merge as XLOOKUP grown up. It does the lookup module's joins over whole tables. What happens on a miss is chosen by the join kind, rather than by error handling. And the whole thing is recorded as a step that runs again.
4. No documented keyboard shortcut for this — use the menu: Append stacks tables that have the same columns, end to end. Use Home → Append Queries for two or three of them. For a folder of monthly files, Data → Get Data → From File → From Folder appends every file and adds the name of the file each row came from. Twelve months become one table, and next month is automatic.
5. No documented keyboard shortcut for this — use the menu: Unpivot undoes the report shape that people like. A grid with months across the top cannot be read by PivotTables or formulas. In the editor, select the identifying columns and use right-click → Unpivot Other Columns. The month columns collapse into two: Attribute, which is the month, and Value. That gives one row per observation. Tall data beats wide data for every analysis tool, and this one verb converts it. Pivot Column exists for the rare journey back.
6. No documented keyboard shortcut for this — use the menu: Group By summarises inside the query. Transform → Group By, grouped by store, with the sum of amount. Use it when the summary itself is the thing you are delivering, worked out before the data ever reaches a sheet. When you want to explore, load the detail and use a PivotTable on the sheet instead.

Try it

Join the lookup workbook's two sheets with a Left Outer merge and expand the fee column. Then run a Left Anti merge and read the report of missing keys. Unpivot the messy workbook's grid of month columns into tall data. Then group that tall data by category inside the query.

Check yourself