Power Query guides for Excel and Power BI
35 practical guides for specific jobs: merge or append, unpivot, locale conversion, combining monthly files, fixing errors and preparing for analyst tests. Every M example on these pages is run by the simulator, and task guides link straight to the exercise that practises the skill.
Looking for something specific? Search lessons, resources and references, or follow the lessons in order.
Power Query in Excel, illustrated
Where everything is in Excel, step by step, with every menu path checked against Microsoft's documentation.
- 1How to open the Power Query editor in ExcelIn Excel for Windows, open the editor from Data > Get Data > Launch Power Query Editor, from Data > From Table/Range with a table selected, or by editing a query in Data > Queries & Connections.
- 2Your first Power Query in Excel, from table to refreshSelect a cell in an Excel table, choose Data > From Table/Range, shape the data in the editor, then choose Home > Close & Load.
- 3Manage Power Query queries in Excel: load, refresh and editClose & Load To chooses where results go.
- 4Power Query in Excel for Windows, Mac and the webExcel for Windows (Microsoft 365) has every Power Query feature.
Guides
- Unpivot columns in Power Query: a refresh-proof guideTurn month-per-column sheets into tidy rows with Unpivot Other Columns, handle totals and blanks, and prove the query survives a new month on refresh.
- Change type using locale: dates and numbers from other countriesFix swapped days and months and misread decimal commas in Power Query with Change Type Using Locale. A diagnosis table, tested examples and practice tasks.
- Combine monthly files into one table with Power QueryStop copy-pasting monthly exports. Combine every file in a folder with one query, skip stray files, keep the file name and check the total after each refresh.
- Move a Power Query query from Excel to Power BI (and back)Copy Power Query queries between Excel and Power BI Desktop: what transfers unchanged and what to fix (sources, Excel-only functions, load settings).
- A four-week Power Query practice planA realistic four-week plan to learn Power Query by doing: 20–30 minutes a day, a lesson then a graded exercise, from first import to merges, reshaping and M.
- Speed up slow Power Query refreshesPractical ways to make Power Query refresh faster: remove columns and rows early, replace row-by-row lookups with merges, buffer small lookups, and load less.
- Power Query for beginners: what it is and where to startNew to Power Query? What it does, where to find it in Excel and Power BI, how a query works, what to learn first, and free exercises to start practising today.
- How to open the Power Query editor in ExcelEvery way to open the Power Query editor in Excel: Get Data, From Table/Range and editing a saved query, plus what each pane of the editor is for.
- Your first Power Query in Excel, from table to refreshBuild your first Power Query in Excel step by step: From Table/Range, filter, add a column, group, Close & Load, then refresh when the table changes.
- Manage Power Query queries in Excel: load, refresh and editWhere Excel keeps Power Query settings: Close & Load To, the Queries & Connections pane, refresh options, duplicate vs reference, parameters and sources.
Comparisons
- Merge vs append in Power Query: which one do you need?Append adds rows, merge adds columns. A side-by-side comparison with tested examples, row-count rules and the exact practice tasks for each.
- Power Query M vs DAX: where each calculation belongsA practical boundary between Power Query (M) and DAX for Excel and Power BI analysts: what to shape at refresh, what to calculate in the model, with examples.
- Power Query in Excel for Windows, Mac and the webWhat Power Query can do in Excel for Windows, Excel for Mac and Excel for the web today: Get Data, the editor, data sources, refresh and known limits.
Troubleshooting
- Resolve Expression.Error in Power Query: a field guideThe common Expression.Error messages in Power Query, what each one means, a reproducible example of each, and the fix. Plus a repair task to practise on.
- Power Query data type pitfalls that quietly change your numbersSeven Power Query data type mistakes that don't raise errors but change results: lost zeros, rounded decimals, text numbers, any columns and more, with fixes.
- Why is my filter keeping blanks? null, empty text and errors in practiceA symptom-led guide to null, empty text and error values across Power Query filters, merges, Group By, fill down, text joins and totals, with tested fixes.
- Power Query error messages: a triage mapSort any Power Query error by its reason (DataFormat, Expression, DataSource, Formula.Firewall, syntax) to know where to look first, with tested examples of each kind.
Recipes
- Power Query data cleaning recipes for messy exportsTen short, tested Power Query recipes for the cleaning jobs analysts meet every week: spaces, case, blanks, codes, junk rows, duplicates and more.
- Reconcile two tables in Power Query (bank vs ledger, system vs system)Build a refreshable reconciliation in Power Query: standardise keys, full outer join, classify each row as matched, amount differs or missing, and total by status.
- Group By recipes: count if, concatenate, first and last, top valuePower Query Group By beyond sums: conditional counts, concatenated lists, first and last dates, the most frequent value and the top item per group. All tested.
- Build a date table in Power QueryCreate a calendar table in Power Query with List.Dates: year, month, quarter, weekday, weekend flag and fiscal year, sized from your data so it refreshes itself.
- Mapping tables vs nested if: categorise values the maintainable wayReplace long if/else chains in Power Query with mapping tables, record lookups and band tables. Unknown values stay visible, and business users can edit the rules.
- Split column recipes: names, codes, addresses and listsPower Query split recipes for real data: split at the last delimiter, by several delimiters, by fixed positions, and into rows, with each splitter tested.
- Running totals and cumulative sums in Power QueryAdd running totals in Power Query, overall and per group, with a method that scales: List.Accumulate or List.Generate over a sorted column instead of per-row sums.
- Power Query for finance: month-end recipesFinance-focused Power Query recipes: signed amounts for credit notes, aged receivables buckets from a fixed report date, currency conversion by month and control totals.
- Top N per group in Power Query, with ties handledKeep the top N rows within each group in Power Query: sort inside Group By, take the first N, and decide whether tied values share a place.
- Compare two snapshots: new, removed and changed rowsCompare last week's and this week's export in Power Query: anti joins for new and removed records, and an inner join that lists exactly which fields changed.
Cheat sheets
- Power Query text functions cheat sheet (tested)The Power Query M text functions analysts use most, with one tested result each: trim, case, length, start/end, split, extract, replace, pad, contains and more.
- Excel formulas and their Power Query equivalentsVLOOKUP, SUMIFS, IFERROR, TRIM, MID, TEXTJOIN and more translated into Power Query steps and M, including the behaviours that differ from Excel.
- SQL to Power Query M: a translation guideTranslate SELECT, WHERE, JOIN, GROUP BY, HAVING, UNION ALL, CASE and COALESCE into Power Query M, and the null comparison rule that differs from SQL.
- Power Query date functions cheat sheet (tested)Everyday Power Query M date functions with one tested result each: parts, names, starts and ends of periods, adding time, week numbers and durations.
Checklists
- Refresh-proof Power Query: a 15-point checklistCheck a Power Query query against 15 things that break on refresh: new columns, renamed headers, new categories, missing files, cultures, positions and more.
- Query folding diagnosis: find the step that stops foldingA step-by-step routine to diagnose slow Power Query refreshes from databases: check the source, find the first non-folding step, reorder, and when native SQL helps.
- Review a Power Query query before you share itA practical review checklist for Power Query queries others will rely on: readable steps, documented decisions, checks, no personal data or credentials, sensible loading.