Query Folding - Transformation

[Amyrie]

Hello everyone, I have a question regarding data transformation and query folding in Power BI, specifically with my database stored in SQL Server.

• Option 1: Prepare the data directly in SQL by importing only the tables and columns I need.

• Option 2: Load the entire original table into Power BI and then transform it as shown in the picture below (is this where query folding comes into play?).

Could someone please explain the difference between query folding and normal data transformation?

As a newcomer to Power BI, I would greatly appreciate your assistance. Thank you!

[Ritaf1983 · Accepted solution]

Hi @Amyrie 

Query folding is a process where Power BI translates the steps you create in Power Query into native SQL queries (or similar) that the source database can execute. Essentially, it allows Power BI to push transformations (like filtering, grouping, or sorting) back to the SQL Server, so the heavy lifting happens on the database side rather than in Power BI. This improves performance because the amount of data transferred is reduced, and the database can handle large datasets more efficiently.

When you load the entire table into Power BI and apply transformations there, query folding will attempt to translate those steps into SQL, but not all transformations are foldable. For example, complex custom columns or merging data from different sources can break query folding, meaning Power BI will pull all the data and apply the transformations locally. This can lead to slower refresh times, especially with large datasets.

**Why Is Query Folding Important for Incremental Refresh?**
Query folding is crucial if you plan to use incremental refresh because this feature relies on the ability to process only new or changed data rather than refreshing the entire dataset. If your transformations break query folding, Power BI won’t be able to utilize incremental refresh effectively, and it will need to reload the whole dataset instead, which defeats the purpose.

**Recommendation:**
If you're aiming for incremental refresh or working with large datasets, it's generally better to prepare and clean the data directly in SQL (Option 1). Use SQL to filter, join, and aggregate the data before importing it into Power BI, ensuring it’s optimized and clean. This way, query folding is preserved, and incremental refresh can run efficiently.

**For More Learning:**
- Check out Microsoft's https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding

for best practices.
- This YouTube video https://www.youtube.com/watch?v=XSVkHS_M94o

provides a comprehensive overview of query folding and its benefits.
- Another useful video :

https://www.youtube.com/watch?v=9Hv8Um4gtdc

By leveraging query folding, you can enhance performance, especially when working with large datasets and incremental refreshes. Make sure to check if your transformations are foldable using the "View Native Query" option in Power Query Editor.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Kedar_Pande · Accepted solution]

@Amyrie 

Query folding refers to the process where Power BI sends the transformations back to the data source (e.g., SQL Server) so that they are processed on the server side instead of in Power BI. This improves performance because the server handles the heavy lifting.

1. Preparing data directly in SQL ensures that transformations are done in the database, maximizing query folding and performance.
2. Loading the full table and transforming in Power BI may limit query folding, causing Power BI to handle the transformations, which can slow things down.

Prefer Option 1 for better performance since it keeps query folding intact.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Kedar_Pande · Accepted solution]

@Amyrie 

Query folding refers to the process where Power BI sends the transformations back to the data source (e.g., SQL Server) so that they are processed on the server side instead of in Power BI. This improves performance because the server handles the heavy lifting.

1. Preparing data directly in SQL ensures that transformations are done in the database, maximizing query folding and performance.
2. Loading the full table and transforming in Power BI may limit query folding, causing Power BI to handle the transformations, which can slow things down.

Prefer Option 1 for better performance since it keeps query folding intact.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Amyrie]

Thank you! If I have a Product table with the columns (ID, Name, Price) and I want to retrieve only the ID and Name columns:

• Option 1: Create a new view or temporary table, Product_A (ID, Name), and then import this table into Power BI.

• Option 2: Load the entire Product table (ID, Name, Price) into Power BI and then remove the Price column.

If you recommend Option 1 for better performance, in what case should we use query folding?

[Anonymous · Accepted solution]

Hi @Amyrie ,

Thanks for replies of Kedar_Pande and Ritaf1983 !

@Amyrie , I am full of doubts about your question.
Query folding is an automatic process, not a manual process. As long as your data source is a relational database (such as SQL Server, MySQL, etc.) and the data transformation operations you do do not destroy query folding, Power Query will automatically fold your query into the data source.
So no matter you use Option 1 or Option 2, Power Query will perform query folding operations automatically, and the performance is similar.

In addition, query folding generally only plays a more important role when you want to perform incremental refresh. If you are not performing incremental refresh, you don't need to pay too much attention to query folding.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983 · Accepted solution]

Hi @Amyrie 

Query folding is a process where Power BI translates the steps you create in Power Query into native SQL queries (or similar) that the source database can execute. Essentially, it allows Power BI to push transformations (like filtering, grouping, or sorting) back to the SQL Server, so the heavy lifting happens on the database side rather than in Power BI. This improves performance because the amount of data transferred is reduced, and the database can handle large datasets more efficiently.

When you load the entire table into Power BI and apply transformations there, query folding will attempt to translate those steps into SQL, but not all transformations are foldable. For example, complex custom columns or merging data from different sources can break query folding, meaning Power BI will pull all the data and apply the transformations locally. This can lead to slower refresh times, especially with large datasets.

**Why Is Query Folding Important for Incremental Refresh?**
Query folding is crucial if you plan to use incremental refresh because this feature relies on the ability to process only new or changed data rather than refreshing the entire dataset. If your transformations break query folding, Power BI won’t be able to utilize incremental refresh effectively, and it will need to reload the whole dataset instead, which defeats the purpose.

**Recommendation:**
If you're aiming for incremental refresh or working with large datasets, it's generally better to prepare and clean the data directly in SQL (Option 1). Use SQL to filter, join, and aggregate the data before importing it into Power BI, ensuring it’s optimized and clean. This way, query folding is preserved, and incremental refresh can run efficiently.

**For More Learning:**
- Check out Microsoft's https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding

for best practices.
- This YouTube video https://www.youtube.com/watch?v=XSVkHS_M94o

provides a comprehensive overview of query folding and its benefits.
- Another useful video :

https://www.youtube.com/watch?v=9Hv8Um4gtdc

By leveraging query folding, you can enhance performance, especially when working with large datasets and incremental refreshes. Make sure to check if your transformations are foldable using the "View Native Query" option in Power Query Editor.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly