Shaping data in Power Query
Power Query is where you clean and reshape data before it reaches the model. The exam tests which transformation fits a described layout.
Pick the right transformation
| Need | Use |
|---|---|
| Add columns from another table by a key | Merge queries (join) |
| Stack tables with the same columns | Append queries (union) |
| Wide table with a column per month or year | Unpivot columns |
| Long table that should be wide | Pivot column |
| Many files in a folder with the same layout | Combine files (Folder connector) |
| Summarize rows | Group By |
Merge supports join kinds: Left Outer, Right Outer, Full Outer, Inner, Left Anti and Right Anti. Anti joins return rows with no match, which is how you find orphan keys.
Unpivot options
Unpivot Columns turns the selected columns into rows. Unpivot Other Columns is safer when new columns may appear later, because it unpivots everything except the ones you keep. The result has two columns, Attribute and Value; rename them.
Reference or duplicate
- Reference creates a new query that starts from the output of the original. Edit the original and the new query follows.
- Duplicate copies all the steps. The two queries then evolve independently.
When several queries derive from one source, keep a staging query, reference it, and turn off Enable load on the staging query so it does not take space in the model. Do not confuse this with clearing Include in report refresh, which still loads the data but skips it during refresh.
Query folding
Folding means Power Query translates your steps into one native query (such as SQL) that runs at the source. Right-click a step and check View Native Query; if it is greyed out, folding stopped at or before that step. Do folding-friendly steps (filters, column removal, joins on the same source) first, and push non-foldable steps such as adding an index late.
Data types and dates
Set correct types early. A text value like 2018-12-31 at 08:59 will not become a date directly; split by delimiter or use Column From Examples, then change the type to Date so the built-in date hierarchy appears.
Errors and connectors
Expression.Error: The column 'X' wasn't foundmeans a step refers to a column that was removed or renamed in the source.- If no native connector exists (for example Cassandra), use a generic one: ODBC is the usual answer.
- Use parameters for server names, file paths or filter values so you can change them without editing M code.
let
Source = Sql.Database(ServerName, "Sales"),
Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
Filtered = Table.SelectRows(Orders, each [OrderDate] >= #date(2025,1,1))
in
FilteredExam traps
- Merge adds columns; Append adds rows.
- Pivot and unpivot are opposites. Wide to long is unpivot.
- Transpose swaps rows and columns but does not create attribute/value pairs.
- Disable load hides a query from the model; it does not stop other queries using it.
- Fewer rows and columns at the source is the fix for timeouts and memory errors, not turning folding off.
Practice questions
Choose an answer, then review the explanation to check your understanding.
1. An Excel sheet has one column per year (2020, 2021) with monthly sales in the rows. You need the columns Month, Sales and Year. What do you select in Power Query Editor?
Show answer
Answer: C.Unpivot columns turns the year columns into attribute/value rows that you can rename to Year and Sales.
2. You have an Orders table and an Order Details table that share Order ID, and you need one query with the unique columns of both. What do you use?
Show answer
Answer: B.Merge queries joins tables side by side on a key (like a SQL join). Append stacks rows.
3. You create Sales Fact and Product Dimension from one Sales Data query. You want little maintenance and a small model. Which approach fits?
Show answer
Answer: B.Reference reuses the source query's steps, so changes flow through. Disabling load on the staging query keeps it out of the model.
4. A refresh fails with Expression.Error "The column 'Category' of the table wasn't found". What is a likely cause?
Show answer
Answer: A.A step such as Changed Type or Removed Columns refers to the column by name, so a rename or removal in the source breaks it.