Speed up slow Power Query refreshes
Guide · By Tahir Nas · Examples run in our simulator · Published · Updated
1. Load less
Remove columns you don't need and filter rows you'll never report on, as early as possible. Against databases those steps may fold; against files they still mean every later step handles less data.
2. Replace per-row lookups with a merge
A custom column that filters another table for every row (each Table.SelectRows(Prices, (p) => p[SKU] = [SKU]){0}[Price]) scans the lookup table once per row. A merge matches all rows in one operation and gives the same result:
let
Orders = #table({"OrderID", "SKU", "Qty"}, {{"A-1", "K-01", 2}, {"A-2", "R-02", 5}, {"A-3", "K-01", 1}}),
Prices = #table({"SKU", "Price"}, {{"K-01", 19}, {"R-02", 6}}),
PerRow = Table.AddColumn(Orders, "Price", each Table.SelectRows(Prices, (p) => p[SKU] = [SKU]){0}[Price], type number),
Merged = Table.ExpandTableColumn(Table.NestedJoin(Orders, {"SKU"}, Prices, {"SKU"}, "P", JoinKind.LeftOuter), "P", {"Price"})
in
#table({"Same result?"}, {{Table.ToRows(PerRow) = Table.ToRows(Merged)}})Result
| Same result? |
|---|
| true |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
3. Buffer small lists used repeatedly
List.Contains(BigList, [Code]) inside each can re-evaluate BigList for every row if it comes from another query. Wrapping it once with List.Buffer keeps a fixed copy in memory for the rest of the evaluation:
let
Allowed = List.Buffer({"K-01", "R-02"}),
Orders = #table({"OrderID", "SKU"}, {{"A-1", "K-01"}, {"A-2", "Z-99"}, {"A-3", "R-02"}})
in
Table.SelectRows(Orders, each List.Contains(Allowed, [SKU]))Result
| OrderID | SKU |
|---|---|
| A-1 | K-01 |
| A-3 | R-02 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Use buffering deliberately: it stops folding for what's buffered, and it holds data in memory. It helps small lookup lists and tables, not large fact tables.
4. Don't load what nobody uses
- Staging queries: connection only (Excel) or Enable load off (Power BI).
- In Excel, avoid loading the same large table to both a sheet and the Data Model.
- In Power BI, remove columns that no visual or measure uses; the model is smaller and refresh is shorter.
5. Fewer, bigger files
A folder of 2,000 tiny CSVs is slower to combine than 24 monthly files with the same rows, because each file has its own open-and-parse overhead. If you control the export, consolidate.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- Overview of query evaluation and query folding in Power Query (Concept, Microsoft Learn)
- Query diagnostics (Concept, Microsoft Learn)
- Best practices when working with Power Query (Concept, Microsoft Learn)
- Table.AddColumn (Function reference, Microsoft Learn)
- Table.SelectRows (Function reference, Microsoft Learn)
- Table.ExpandTableColumn (Function reference, Microsoft Learn)
- Table.NestedJoin (Function reference, Microsoft Learn)
- Table.ToRows (Function reference, Microsoft Learn)
- List.Buffer (Function reference, Microsoft Learn)
- List.Contains (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.