How to check Query folding in Power Query?

What’s Query folding?

Query folding is the ability of Power Query (i.e. Mashup engine) to generate a single query with all the required clauses and filters. This single query is used to retrieve and transform the source data.

Why is it important?

• It pushes the complex transformation to the source.
• As you get the required data transformed right away, it improves efficiency and overall performance.

How to check if your Query is folding?

1. View Native Query

If the source is relational DB/ SQL then just check if the “View Native Query” is enabled or grayed out. If it’s enabled then it will show you the single query that will be used to retrieve the data. If it’s grayed out then it means some steps are not supported in query folding.

Query Settings > Right click on the last step > Click “View Native Query”

2. Get Metadata

In sources like Snowflake, the view native query option isn’t supported and is grayed out even of the query is actually folded. In such cases, you can use “GetMetadata” Power query option.

Just add “GetMetadata” and “Queryfolding” step in the advanced editor code. It should look something similar to below.

let
     Source = ####,
     TableName = ###,
     GetMetadata = Value.Metadata(TableName),
     QueryFolding = GetMetadata[QueryFolding]
in
     QueryFolding

Once you click enter you will see if the “IsFolded” is true or false which should be be an indicator of the query folding.

Final thoughts

Query folding is very important for improving the performance in terms of ingesting the data from the source to Power BI. There are some scenarios where the query folding doesn’t work and there nothing much a developer can do about it. In this post, my main focus was to share the different techniques you can use to check if the query is folding or not.