Query folding in Power Query in Power BI - Step-by-Step Guide

Introduction

Query folding helps Power Query send data steps back to the source system to run there. This makes data loading faster and uses less computer power on your machine.

When you want to speed up data refresh by letting the database do filtering or calculations.

When your data source supports query folding, like SQL Server or Azure SQL.

When you want to reduce the amount of data transferred over the network.

When you apply filters or transformations that the source can handle directly.

When you want to keep your Power Query steps efficient and scalable.

Steps

Step 1: Open your Power BI Desktop file

- Home tab > Transform data

Power Query Editor window opens showing your data queries

Step 2: Right-click on a query name

- Queries pane on the left

Context menu appears with options for the query

Step 3: Select 'View Native Query' option

- Context menu after right-clicking the query

A window shows the SQL or native command that Power Query will send to the data source if folding is working

Step 4: Apply a filter or transformation step

- Power Query Editor ribbon or right-click column

The query updates and you can check again if 'View Native Query' shows the updated command

Step 5: If 'View Native Query' is greyed out, check previous steps

- Applied Steps pane on the right

You find which step breaks query folding by disabling or removing it and testing again

Before vs After

Before

Query applies all steps locally, loading full data from source, taking 5 minutes

After

Query folding sends filters to source, loading only needed data, taking 30 seconds

Settings Reference

View Native Query

📍 Right-click query name in Queries pane

Shows the exact query sent to the data source to confirm folding

Default: Enabled when folding is possible

Enable Load

📍 Right-click query name > Enable Load

Controls if the query loads data to the model; folding only applies if loading

Default: Checked

Common Mistakes

Adding a step that Power Query cannot fold, like a custom column with complex M code

This breaks query folding and forces all data to load locally, slowing refresh

Use simple transformations supported by the source or do complex steps after folding ends

Trying to fold queries from sources that do not support folding

Query folding only works with certain data sources like SQL databases

Check source compatibility and avoid expecting folding on unsupported sources like Excel files

Summary

Query folding pushes data steps back to the source to improve speed and efficiency.

You can check folding by using 'View Native Query' in Power Query Editor.

Avoid steps that break folding to keep your data refresh fast.