Power Query

Every month the same export lands in your inbox, and every month you do the same ten things to it: delete the columns nobody needs, drop the cancelled orders, fix the prices that came in as text, add a total. Power Query is the part of Excel that records those ten things once, as a list of Applied Steps, and replays them on next month’s file with one click on Refresh.

In this chapter you work in a practice editor built for this site. It is not Power Query and it does not run Excel; it gives you Power Query’s way of working: you start from an imported table (the Source step), add steps one per line, watch the table change after each one, and the checker compares the final table with the answer. The step names, the order you add them in and the results match what Power Query does, so the thinking carries straight over. Every lesson also gives the real Excel path (Data → Get Data → … → Transform Data) and the M code the step writes, so you can do the same thing at your desk.

You will clean a marketplace order report, split a payment code to recover the order number, build totals and a month column, look up customer discounts with Merge, stack two quarters with Append, see why importing from SQL Server with a query beats importing a whole table, and finish with a boss level that turns a messy export into a revenue summary ready for a PivotTable.