<!-- https://learnsql.cpetoday.com/analyze/power-query-m-performance -->
# M performance, errors and when to use M

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 `each` that 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.

> Diagram: Steps that fold run as one SQL query on the server and return a small result. A step that does not fold, such as adding an index column, makes every row come to your PC.

How to see it:

1. Right-click a step in Applied Steps.
2. 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:

1. **Filter rows and remove columns first.** Both fold, and they shrink everything after.
2. **Put the steps that cannot fold last.**
3. **Avoid per-row functions on database tables** (see [functions and patterns](/analyze/power-query-m-patterns)).
4. 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 `SELECT`s.

## 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](/analyze/fundamentals). |
| A date table | **M** or DAX | Both work; see [M functions and patterns](/analyze/power-query-m-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](/analyze/power-query-lab) holds 86 tables built for practice, one per skill, each deliberately messy.
- `lab_hdr_title_rows` is the first to try: remove top rows, promote headers.
- `lab_piv_sales_wide` practices unpivoting.
- `core_orders`, `core_customers` and `core_products` are 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](/analyze/relationships).

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](/analyze/power-query-m-patterns).
Series: [basics](/analyze/power-query-m-basics), [tables](/analyze/power-query-m-tables), [functions and patterns](/analyze/power-query-m-patterns), [performance](/analyze/power-query-m-performance).
