M performance, errors and when to use M
Query folding, the errors you will meet and how to fix them, and when to use M, SQL or DAX.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Explain query folding and why the order of your steps matters.
- Diagnose the common M errors and fix them.
- Decide when a job belongs in M, in SQL or in DAX.
- Practice these ideas on the sample data.
Part 4 of 4: query folding, the errors you will meet, how M compares with SQL and DAX, and where to practice.
Query folding: why order matters
When your source is a database, Power Query tries to fold your steps into one SQL query that runs on the server. Filters, column selection, simple group by, sort and joins usually fold. Then the database does the heavy work and sends back only the answer.
Steps that cannot be written in SQL stop the folding. From that step on, Power Query pulls every row to your PC and works there. Common culprits:
- a custom function or
eachthat uses a function SQL has no match for; Table.Buffer;- adding an index column, or
List.Accumulate; - merging two queries that come from different sources.
How to see it:
- Right-click a step in Applied Steps.
- If View Native Query is available, the step folds. If it is greyed out, folding stopped at or before that step.
Rules that keep queries fast:
- Filter rows and remove columns first. Both fold, and they shrink everything after.
- Put the steps that cannot fold last.
- Avoid per-row functions on database tables (see functions and patterns).
- For very large tables, write the join or aggregate in SQL and let Power Query load the result.
Value.NativeQuery lets you send your own SQL: Value.NativeQuery(Source, "SELECT TOP 100 * FROM orders"). The sample databases are read-only, so use it for SELECTs.
When something goes wrong
| Message | What it means | Fix |
|---|---|---|
Expression.Error: The column 'X' of the table wasn't found. |
A step names a column that no longer exists or has different capitalization. | Check the spelling and case. Look at the step before. If the source column was renamed, change the name here. |
DataFormat.Error: We couldn't convert to Number. |
A cell holds text such as N/A or 1,234 USD. |
Clean first with Text.Replace, then convert, or use try ... otherwise null. |
Expression.Error: We cannot convert the value null to type Text. |
A text function got null. |
Provide a default: Text.Trim([name] ?? ""). |
Token Comma expected / Token Literal expected |
Missing or extra comma, bracket or quote. | Check the line above the one highlighted. |
Expression.Error: The name 'X' wasn't recognized. |
Spelling, case, or the step is defined below where you use it. | Fix the name. The order of steps does not matter to M, but names must exist. |
Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. |
Two sources with different privacy levels are being mixed. | In Data source settings, set both to the same privacy level, or split the work into two queries. |
| A merge returns no matches | Different types (text vs number) or stray spaces. | Match the types and Text.Trim the key. |
M, DAX and SQL: which to use
| Job | Best tool | Why |
|---|---|---|
| Clean and reshape data (split, unpivot, fix types, combine files) | M | Runs at refresh time; results are stored. |
| Join and aggregate big tables | SQL (or M that folds) | The database is the fastest place to do it. |
| Measures that react to slicers (totals, ratios, YTD) | DAX | Calculated when someone filters the report. See DAX fundamentals. |
| A date table | M or DAX | Both work; see M functions and patterns. |
| A calculation on one row | M column or DAX column | Prefer M or SQL; DAX columns increase model size. |
A good rule: shape the data in M or SQL, then calculate in DAX.
Practice with the sample data
- The Power Query playground holds 86 tables built for practice, one per skill, each deliberately messy.
lab_hdr_title_rowsis the first to try: remove top rows, promote headers.lab_piv_sales_widepractices unpivoting.core_orders,core_customersandcore_productsare a clean retail set for merges and groups.- After you can write a step by hand, build the same query in DAX in Relationships and the data model.
Three habits make the biggest difference: read the M the editor writes, filter early, and name your steps so the Applied Steps list tells the story.
Previous: M functions and patterns. Series: basics, tables, functions and patterns, performance.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.