top of page
  • Gold-Service partner
  • LinkedIn

Speed up your Power query with Query Folding

  • Writer: Sofia Ng
    Sofia Ng
  • Jun 16
  • 3 min read

If you’ve ever wondered why some queries run faster, refresh cleaner, or scale better than others, chances are the answer is hiding in something called query folding.

It doesn’t sound glamorous. But once you get it, you’ll see it everywhere and it might just change how you design every Power Query workflow from here on out.


Librarian cat on a power query logo

Query folding is when Power Query translates your steps into native code for the underlying data source (like SQL for databases). Instead of importing all the data and filtering it afterward, Power Query says, "Hey data source, just send me what I need."


It's the difference between:

  • Downloading an entire book and searching for a quote manually, or

  • Asking the librarian to hand you the right page


Why It Matters

When folding works, it means:

  • Less data transfer = faster queries

  • Better performance at scale

  • More efficient refreshes in Power BI

  • Cleaner handoffs

When it doesn’t work, Power Query downloads everything, performs filtering locally, and often slows down your report or dashboard.


How to Check If Your Steps Are Folding

Power BI Desktop makes this (relatively) easy:

  1. Right-click a step in the Applied Steps pane

  2. Choose "View Native Query"

  3. If it’s clickable = your query is folding. If it’s greyed out = you’ve lost folding


Common Folding Breakers

Some steps kill folding immediately. Here are a few usual suspects:

  • Adding an Index Column

  • Merging queries using fuzzy match

  • Custom columns with non-foldable logic

  • Changing data types in inconsistent ways

  • Using Table.Buffer() too early


Tip: Do heavy transformations after the foldable steps. Treat folding like a stack of glassware—delicate, and easy to break if you shift things too soon.


Custom Columns with NonFoldable Logic

When you add a custom column in Power Query using functions like Text.Contains, DateTime.LocalNow, or if...then...else, Power Query may no longer be able to translate that logic into native SQL (or whatever language your data source uses). That’s when query folding breaks.

Why? Because most databases don’t support Power Query’s M language, and if Power Query can't map your function directly to a native operation, it gives up on folding and processes the entire dataset locally.

Example:

Table.AddColumn(Source, "Recent?", each [OrderDate] > DateTime.LocalNow())

This breaks folding because DateTime.LocalNow() is evaluated in Power Query, not in the source system. The query must pull all the rows before it can filter them.


Better Approach

Where possible:

  • Move logic like this to later in your query, after foldable steps (e.g., filters, joins).

  • Replace with foldable equivalents if using a SQL source (e.g., write the logic in SQL Views or push calculations upstream).

  • Use custom columns only after you've already reduced the dataset with folding-preserving steps.



Folding-Friendly Best Practices

Push filters and transformations upstream. Apply them as early as possible, especially in SQL-based sources.

Avoid previewing data constantly during build. Each preview can trigger partial evaluations and “quietly” break folding.

Don’t assume Excel = no folding. Even Excel and SharePoint folders fold some operations under the hood.

Split queries into staging and transformation layers. Use the staging query to preserve foldability, then do complex work in a dependent query.


Use Diagnostics to Trace Folding

Power BI Desktop’s Query Diagnostics tool shows exactly what gets pushed to the source vs. done locally. Use it to:

  • Validate folding behavior

  • Spot bottlenecks in refresh performance

  • Confirm how Power Query is translating your logic


Quick Folding Recap

  • Query folding = doing the work at the source, not in Power Query.

  • It’s the key to speed, scalability, and sanity in Power BI.

  • The moment you break it, performance can degrade, sometimes drastically.

  • Learn to test for it, preserve it, and architect with it in mind.


If you or someone in your team needs some help with query folding, send us a message. We would love to chat.

Gold medal Make Partner

Contact Us

QUESTIONS?

WE'RE HERE TO HELP

  • LinkedIn

© 2023 by Ava Technology Solutions. Proudly created with Wix.com

bottom of page