Power Query blog
50 articles on getting real work done with Power Query in Excel and Power BI: cleaning exports, building finance and operations reports, writing better M and fixing what breaks. Every M example is run in our simulator when the site is built.
For step-by-step teaching, follow the lessons; for quick recipes, see the guides. RSS feed.
How-to
- 10 Power Query habits that keep queries easy to maintainTen Power Query best practices that keep queries readable and refresh-safe: step names, types, early filters, mapping tables, parameters, checks and more. · 10 min read
- Calculate working days between two dates in Power QueryPower Query has no NETWORKDAYS function. Count working days between two dates in M with a holiday list, any weekend pattern, and a fast calendar for big tables. · 11 min read
- Clean survey data in Power Query: multi-select answers to rowsSplit multi-select survey answers into one row per option in Power Query, count respondents per option correctly, map spelling variants and pivot back to flags. · 9 min read
- Cleaning phone numbers and postcodes with Power QueryStandardise UK phone numbers and postcodes in Power Query: keep only digits, fix +44 and lost leading zeros, re-space postcodes and flag values that fail. · 11 min read
- Convert Unix timestamps and Excel serial dates in Power QueryTurn Unix epoch seconds or milliseconds and Excel serial numbers like 46023 into dates in Power Query, with tested M for text, nulls and 1904 workbooks. · 10 min read
- Currency conversion with an exchange-rate table in Power QueryConvert foreign-currency amounts in Power Query: merge a monthly or daily rate table, take the last rate on or before each date, and round with intent. · 9 min read
- Fiscal years and fiscal quarters in Power QueryAdd fiscal year, quarter and month columns in Power Query for any start month, label them FY25/26 style, and handle a tax year that starts on 6 April. · 11 min read
- Fuzzy matching in Power Query: standardise inconsistent namesStandardise inconsistent product names in Power Query: normalise text first, let fuzzy merge suggest matches, then keep checked matches in a mapping table. · 11 min read
- How to get data from another Excel workbook with Power QueryPull a table or sheet from another Excel workbook with Power Query, read the M it writes, and keep the query working when the file moves or changes. · 10 min read
- How to refresh Power Query automatically in ExcelRefresh Power Query when an Excel workbook opens, every few minutes or on a timer with VBA, and make the queries safe to refresh when nobody is watching. · 9 min read
- How to split full names into first and last names in Power QuerySplit a full name column into first and last names in Power Query, with rules for middle names, single names, 'Last, First' order, titles and suffixes. · 9 min read
- Keep or remove leading zeros in Power QueryKeep leading zeros in Power Query by typing codes as text, put lost zeros back with Text.PadStart, and remove them safely with Text.TrimStart. · 9 min read
- Naming queries and steps in Power Query: a convention that scalesA simple naming convention for Power Query: role prefixes for queries, result-describing step names, and readable output columns, with tested M examples. · 10 min read
- Reduce Power BI file size with Power QueryShrink a Power BI model before it loads: keep needed columns and rows, summarise, split high-cardinality columns, fix types and stop staging queries loading. · 10 min read
- Switch between test and production sources with Power BI parametersPoint one Power BI file at a test or production source with a single parameter, check the value safely, and change it in the service or a deployment pipeline. · 12 min read
- Text.Format in Power Query: build dynamic text cleanlyUse Text.Format in Power Query to build labels, messages and file names from placeholders, with tested examples for rows, nulls, cultures and templates. · 8 min read
- Use an Excel cell as a Power Query parameterRead a date, number, text or file path from a named Excel cell into Power Query, use it to filter a query, and avoid the Formula.Firewall error. · 10 min read
Use case
- Allocate shared costs across departments with Power QueryAllocate rent, IT and other shared costs in Power Query: driver tables, department shares, a merge that spreads each cost, and rounding that ties out. · 11 min read
- Budget vs actual reporting with Power QueryBuild a budget vs actual report in Power Query: unpivot a wide budget, total actuals by month, full outer join both sides, then label variances correctly. · 10 min read
- Build a monthly HR headcount report with Power QueryTurn an employee list with start and leave dates into month-end headcount, FTE by department, and starters and leavers that reconcile, using Power Query. · 12 min read
- Build an aged receivables report with Power QueryBuild an aged debtors report in Power Query: days overdue from a fixed as-of date, ageing buckets from a table, part payments and one row per customer. · 12 min read
- Combine marketing exports from several ad platforms in Power QueryCombine ad platform exports with different column names, date formats and number styles into one Power Query table, then calculate CTR and CPC correctly. · 11 min read
- How to clean a messy CRM export in Power QueryClean a CRM export in Power Query: tidy names and emails, turn blanks into null, map country spellings, remove duplicate contacts and list problem rows. · 13 min read
- Replace a monthly copy-paste process with Power Query, step by stepTurn a monthly routine of opening exports, pasting rows and fixing formats into one Power Query that combines a folder of files and refreshes in a click. · 10 min read
- Stock on hand from inventory movements in Power QueryTurn a log of receipts, issues, transfers and adjustments into stock on hand, stock at any date, running balances and month-end snapshots in Power Query. · 12 min read
- Tiered sales commission calculations in Power QueryCalculate tiered sales commission in Power Query from a rate table: marginal and whole-amount tiers, monthly totals per rep, rounding and tier-table checks. · 11 min read
- Turn a bank statement CSV into ledger-ready rows with Power QueryClean a bank statement CSV in Power Query: skip banner rows, read UK dates and amounts, sign paid in and out, map nominal accounts and prove the balance. · 14 min read
Comparison
- Load to a table or the Data Model? Choosing where Power Query results goWhen to load a Power Query result to a worksheet table, to the Data Model or as connection only, with the limits, trade-offs and steps to switch. · 9 min read
- Power BI Desktop or dataflows: where should Power Query steps live?Decide which Power Query steps belong in a Power BI dataflow and which stay in the Desktop file, with a comparison table, tested M and a checklist for moving. · 12 min read
- Power Query vs Power Automate: which tool for which jobPower Query shapes data; Power Automate runs workflows. Which jobs belong to each, where they overlap, and how to combine them without duplicating logic. · 9 min read
- Power Query vs Python pandas for data cleaningPower Query or pandas for cleaning data? The same tasks side by side (unpivot, merge, locale parsing), where each tool fits, and how they work together. · 9 min read
- Power Query vs VBA macros: when to switchWhen to replace a VBA macro with Power Query: which data jobs move across, which still need VBA, and how to migrate a macro without breaking the report. · 9 min read
- Transform in SQL, Power Query or DAX? Where each step belongsDecide whether a Power BI transformation belongs in the SQL source, in Power Query or in DAX, with a placement table, tested M examples and the ratio trap. · 11 min read
Explainer
- Incremental refresh in Power BI: what your Power Query must get rightThe Power Query side of incremental refresh: RangeStart and RangeEnd, a half-open date filter, date vs datetime columns, integer date keys and query folding. · 13 min read
- List.Generate in Power Query: loops without the confusionHow List.Generate works in Power Query M: the four functions, record state, the selector, the off-by-one trap and loops for dates, balances and paging. · 11 min read
- Power Query in Excel for Mac: what works and what doesn'tWhat Power Query in Excel for Mac can do, per Microsoft's documentation and Excel team posts: the editor, data sources, file paths, sharing with Windows. · 11 min read
- Recursive functions in Power Query M with the @ operatorRecursive functions in Power Query M: why the spec needs @ for a function to call itself, a no-@ alternative, a management chain and loop guards. · 10 min read
- Table.Buffer and List.Buffer: when buffering helps and when it hurtsWhen Table.Buffer and List.Buffer make Power Query faster or more predictable, when they slow it down by blocking query folding, with tested M examples. · 9 min read
- Time zones in Power Query: DateTimeZone without the confusionHow datetime and datetimezone differ in Power Query, how to convert to UTC or UK time with summer time, and why refreshes in the cloud see a different now. · 11 min read
- try … catch in Power Query M: the newer error-handling syntaxHow try … catch works in Power Query M: catch with or without a parameter, reading the error record, re-raising errors, and how it compares with otherwise. · 8 min read
- Week numbers in Power Query: ISO weeks vs Date.WeekOfYearDate.WeekOfYear restarts at week 1 every 1 January, so it often differs from ISO 8601 weeks. See when and why, and add a tested ISO year and week column in M. · 11 min read
- Why your Excel report breaks every month, and how Power Query fixes itMonthly Excel reports break when the export changes shape. See the usual causes and the Power Query steps that let a report survive each refresh. · 11 min read
Troubleshooting
- Case sensitivity in Power Query: the silent cause of missing matchesPower Query treats upper and lower case as different, so Ben@Shop.com won't match ben@shop.com. Fix merges, filters and groups with a key or a comparer. · 9 min read
- CSV quirks in Power Query: line breaks, quotes and delimiters inside valuesFix CSV imports that split rows or shift columns: quoted line breaks, commas inside values, doubled quotes, semicolon files and the fixed column count. · 10 min read
- Handle changing column headers from a source system in Power QueryStop refreshes failing when a source renames or adds columns: clean names, rename from a mapping table, check the schema early, then select and type by rule. · 10 min read
- Power Query row count is wrong? How to find where rows go missingFind the Power Query step that loses rows: count rows after every step, list the dropped rows with an anti join, and fix the five usual causes. · 11 min read
- Why dates turn into numbers after Power Query loads to ExcelDates that load to Excel as 46296 are Excel serial numbers. Find the untyped column in Power Query, set a Date type, and stop the table losing its format. · 10 min read
Career
- How long does it take to learn Power Query? A realistic timelineA stage-by-stage timeline for learning Power Query, from a first useful query to writing M functions, with a self-test for each stage. · 10 min read
- Is Power Query worth learning? Who it helps and who can skip itWho gets real value from learning Power Query, who can safely skip it, how the skill builds in stages, and what to learn first if you decide it pays off. · 9 min read
- Power Query skills on a CV: what to list and how to prove itWhich Power Query skills to put on a CV, how to word them as outcomes rather than buttons, and how to prove them with a small portfolio and a live test. · 12 min read