Power Query M: The 10 Transformations You Must Know for PL-300

Every PL-300 cohort I run, someone asks the same question after their first practice exam: "why did I lose points on the Power Query questions when I use it every day?" The answer is usually that daily use teaches you to click buttons in the Power Query Editor without thinking about what M step each button generates — and the exam tests the transformation, not the click path.

Here are the ten transformations that come up most, with the mechanics behind each one.

1. Remove/keep columns

The simplest transformation, and an easy way to lose exam points anyway: know the difference between Remove Columns (deletes the ones you select) and Remove Other Columns (keeps only the ones you select, deletes everything else). Both generate Table.RemoveColumns, but the second one is more resilient — if a new column shows up in the source later, Remove Other Columns keeps your query working exactly as designed, while a plain Remove Columns silently lets the new column through.

= Table.RemoveColumns(Source, {"InternalNotes", "TempFlag"})

2. Change type

Table.TransformColumnTypes sets the data type of one or more columns. This looks trivial but has real consequences: an incorrectly typed column (text instead of whole number, for example) breaks aggregations, sorts incorrectly, and can silently break query folding against a database source. Always check the type indicator in each column header after a transformation — Power Query sometimes infers a type differently than you expect, especially on the first load from a CSV.

3. Split column

Splitting by delimiter, by number of characters, or by positions is one of the most common cleanup steps, and PL-300 likes to test scenario questions around it — for example, splitting a single "FirstName LastName" column into two, or extracting a product code prefix from a longer SKU string.

= Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" "), {"First", "Last"})

4. Unpivot columns

This is the transformation most candidates underestimate. Source data — especially from Excel exports — often arrives "wide," with a separate column per month or per category (Jan, Feb, Mar...). Power BI wants that data "long" — one row per date/category combination — so it can be filtered, sliced, and aggregated properly. Unpivot Columns turns wide data into long data.

= Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")

Know the difference between Unpivot Columns (unpivot only the ones you select) and Unpivot Other Columns (unpivot everything except the ones you select). The second is the safer default for the same reason as Remove Other Columns: it's resilient to new columns appearing in the source.

5. Pivot column

The reverse of unpivot — takes long data and spreads one column's distinct values into new columns, aggregating another column into the cells. Less common in practice than unpivot, but the exam tests that you know it exists and when you'd reach for it (typically: preparing a summary table for a specific visual, not for the underlying model).

6. Merge queries

Merge Queries is Power Query's join operation — it matches rows between two queries based on one or more key columns, similar to a SQL join. PL-300 expects you to know the join kinds: Left Outer (all rows from the first table, matching rows from the second — the default and most common), Inner (only matching rows from both), Right Outer, Full Outer, and the anti-joins (Left Anti and Right Anti, which return only the unmatched rows — useful for finding orphaned records).

7. Append queries

Not to be confused with merge. Append stacks two or more queries with the same (or similar) columns on top of each other — combining multiple months of sales exports into one table, for example. Merge adds columns; append adds rows. This distinction is tested directly and often confused by candidates coming from an Excel VLOOKUP background where the concept of "stacking" tables isn't as explicit.

8. Group by

Table.Group aggregates rows — sum, average, count, min, max, or a custom aggregation — based on one or more grouping columns. It's the Power Query equivalent of a SQL GROUP BY or a pivot table's summarization. Useful for pre-aggregating a large fact table before it loads into the model, which reduces model size when you don't need row-level detail.

9. Custom columns and conditional columns

Table.AddColumn (custom column, written in M) and its no-code cousin, the conditional column builder, both add a new calculated column during the query stage — as opposed to a DAX calculated column, which is added after the data loads into the model. Know when each is appropriate: transformations that reduce the row count, change data types, or need to happen before the model exists belong in Power Query. Business logic that depends on filter context or relationships belongs in DAX, after load.

10. Query folding (the concept that ties it all together)

Query folding is not a single transformation — it's a property of how Power Query executes your steps. When your data source is a database (SQL Server, an Azure data source, a Fabric warehouse), Power Query tries to translate your applied steps back into a single native query and push execution to the source, instead of pulling all the raw data into Power BI and transforming it locally. This is dramatically faster and lighter on both memory and refresh time.

Folding breaks when a step can't be translated to the source's query language — a common example is a custom M function, certain text transformations, or adding an index column. In the Applied Steps pane, right-click a step: if "View Native Query" is available (not greyed out), that step still folds. The moment a step breaks folding, every subsequent step runs locally, no matter how simple.

Practical rule the exam tests directly: order matters. Put transformations likely to break folding (custom columns, complex text operations) as late as possible in your applied steps, after the folding-friendly transformations (filters, column selection, type changes, simple merges) have already reduced the amount of data Power Query needs to pull locally.

Why this domain gets underrated

"Prepare the data" is only 15–20% of the exam by weight, next to Modeling's 30–35%, so candidates often study it last and lightest. But a meaningful share of the exam's scenario and case-study questions describe messy source data and ask what transformation — specifically — fixes it. If you can't immediately name whether a described problem is an unpivot, a merge, or a folding issue, you'll burn time reasoning it out during the exam instead of recognizing it. Practice with genuinely messy sample data, not just clean textbook examples, and this domain stops being the one you rushed through.