Power Query is easy to learn by clicking and hard to keep working, because almost nothing that goes wrong goes wrong while you are building it. The editor shows a preview that is correct; the failure arrives days later, on a refresh, often on someone else's machine.
Three causes account for most of it. A query built by browsing to a file records that absolute path, so the query breaks the moment the file moves or the workbook is opened elsewhere. The Changed Type step that Power Query inserts automatically at import hard-codes every column name, so an upstream rename fails the refresh even if you never used that column. And merges change row counts without warning — a Left Outer join multiplies rows when the right-hand table has duplicate keys, and an Inner join silently drops rows that did not match.
Everything below applies to Power Query in Microsoft 365 and Excel 2021, and the same behaviour appears in Power BI. Work each problem before opening the answer.
Three causes, five problems
The query breaks on someone else's machine
1 problemA query built by clicking Get Data records the absolute path of what you picked — C:\Users\you\Documents\sales.xlsx — inside the query itself. Everything works until the file moves, the folder is renamed, or a colleague opens the workbook on their own machine, at which point the refresh fails with a data-source error rather than falling back to anything. The durable pattern is to put the path in a cell, load it as a parameter, and have the query reference the parameter, so relocating the data is a one-cell edit rather than a trip into the editor.
How it shows up: "Power Query can't find the file" · "DataSource.Error: file not found"
Q1
You built a query from a file in your Documents folder and emailed the workbook to a colleague. They click Refresh All. What happens?
- AIt works — the data is already loaded into the workbook
- BThe refresh fails, because the query stores your absolute path and that path does not exist on their machine
- CExcel prompts them to browse for the file automatically
- DIt works but returns only the rows that were cached
▶Show answer & explanation
Answer: B. The refresh fails, because the query stores your absolute path and that path does not exist on their machine
🐱 The loaded results do travel with the workbook, so the table looks fine until someone refreshes — which is exactly what makes this confusing to diagnose. The query step itself holds your path, and on another machine that path resolves to nothing, so the refresh raises a data-source error. Parameterising the path is the fix; a shared network location or SharePoint source also works, because then the path is the same for everyone rather than being tied to one profile.
Why a query that worked yesterday fails after the source gains a column
1 problemPower Query records what you did as a literal list of steps, and several of those steps hard-code column names — Changed Type lists every column by name, and so do Removed Columns and Renamed Columns. Rename a column upstream, or have a source that emits columns in a different order, and the step that names it fails with 'column not found'. The habit that prevents most of this is to delete the automatic Changed Type step that Power Query inserts at import and set types once at the very end, after the shape of the table has settled.
How it shows up: "Column not found" after refresh · "Expression.Error: The column 'X' of the table wasn't found"
Q2
Your query imports a CSV. The supplier renames "Amount" to "Amount (USD)". The refresh fails at the Changed Type step. Why does that step care about a column name?
- AChanged Type validates that every column still has its original data type
- BChanged Type stores an explicit list of column names and their types, so a renamed column is simply missing
- CCSV imports always fail when headers change
- DThe step needs to be refreshed manually after any schema change
▶Show answer & explanation
Answer: B. Changed Type stores an explicit list of column names and their types, so a renamed column is simply missing
🐱 Changed Type is not a general instruction to detect types — it is a recorded list along the lines of 'Amount → number, Date → date, Region → text'. When Amount no longer exists, that entry cannot be applied and the step errors. Because Power Query inserts this step automatically on import, it is the single most common source of refresh breakage. Deleting the auto-inserted step and setting types once at the end makes the query tolerant of upstream renames that do not affect the columns you actually use.
Merge queries: the join kind that silently changes your row count
2 problemsMerge asks for a join kind and the default, Left Outer, keeps every row from the first table and attaches matches from the second. Two things then routinely surprise people. If the second table has more than one row per key, a left join multiplies rows rather than picking one — a hundred-row table can come back with a hundred and forty. And an Inner join, which people often select because it sounds tidier, silently drops every first-table row that had no match. Neither shows an error, so the only reliable check is to compare row counts before and after.
How it shows up: "Power Query merge duplicates rows" · "Merge lost rows"
Q3
You merge a 100-row orders table with a customers table using a Left Outer join on customer ID. The result has 140 rows. What does that tell you?
- AThe merge failed and duplicated the orders table
- BThe customers table has multiple rows for some customer IDs, and a left join returns one output row per match
- CLeft Outer joins always add rows; Inner would have kept 100
- DForty orders had no matching customer
▶Show answer & explanation
Answer: B. The customers table has multiple rows for some customer IDs, and a left join returns one output row per match
🐱 A left join guarantees that every row of the first table survives, not that the row count stays the same — where the second table has three rows for one key, that order comes back three times. Forty extra rows therefore means duplicate keys on the right, and the customers table is not unique by customer ID the way you assumed. The fix is upstream: deduplicate the customers table, or aggregate it to one row per ID before merging. Checking the row count after every merge is the habit that catches this, because nothing about the result looks wrong on screen.
Q4
You switch that merge to an Inner join and the result drops to 88 rows. What happened to the other 12?
- AThey were duplicates and got removed
- BThey had no matching customer ID, and an Inner join keeps only rows that match on both sides
- CThey failed the data type conversion
- DInner joins sample the data for performance
▶Show answer & explanation
Answer: B. They had no matching customer ID, and an Inner join keeps only rows that match on both sides
🐱 Inner keeps only rows present on both sides, so twelve orders reference a customer ID that the customers table does not contain — orphan records, usually a sign of a data-quality problem worth knowing about rather than silently discarding. This is the argument for starting with a Left Outer join even when you expect a perfect match: the unmatched rows come back with nulls, which makes the problem visible instead of making it disappear.