Faced with ever-increasing volumes of data, data experts have a number of tools at their disposal to simplify data processing. These include query folding on Power BI Desktop or Power Pivot. With Query Folding in Power Query, you can considerably enhance your data analysis capabilities.
So what is Power Query? How does it work? And above all, how do you use it? DataScientest answers your questions.
What is Query Folding in Power Query?
Query folding involves generating a single instruction to retrieve and transform source data. This simplifies the processing of large volumes of data.
Query folding is particularly useful for designing data models in Power Pivot or Power BI Desktop.
Good to know: Query folding can be used for an entire query (full folding) or just a few steps (partial folding).
How does Query Folding in Power Query work?
To use query folding, you need to write a set of statements in SQL code and transmit them to the database for execution. The idea is then to reduce the amount of data at source before processing and transferring it to Power BI or Power Pivot. However, for query folding to work properly, the following conditions must be met:
Data source supports query folding
Rest assured, the majority of sources accept query folding. These include relational databases, OData feeds, SharePoint Lists, Azure Synapse Analytics, Exchange, HDFS Active Directory and more.
On the other hand, if your data comes from flat files, blob objects or the web, you won’t be able to use query folding.
Transformation enables data to be folded
In addition to data sources, the configuration steps must also enable query folding. Typically, these are transformations that can be written as a single SELECT statement. Here are a few examples:
- column deletion ;
- row filtering (with a WHERE query) ;
- data grouping (with a GROUP BY query) ;
- non-approximate merging (with a JOIN query);
- adding or deleting a pivot table.
If, among all the instruction steps, only one action cannot be folded, query folding will stop for the entire query. Here are some examples of transformations that prevent query folding:
- merging or adding queries based on different sources ;
- adding custom columns with complex logic;
- adding index columns.
Generally speaking, you’ll have trouble using query folding when the transformation involves several data sources.
Good to know: if query folding can’t be obtained, the Mashup Power Query Engine takes over, transforming the data itself.
But this operation is generally slower, especially if you need to process large volumes of data.
The request can be folded
To find out whether a query can be folded, follow the steps below:
- In the Power Query editor, go to the query’s “Settings” tab.
- Right-click on the last step applied;
- Check the “Show native query” option. If enabled (not grayed out), the query can be folded.
- If not, the query cannot be folded.
Good to know: this “Show native query” option is only available for certain connectors.
For example, it is not available for OData connectors. If this is the case, use the “Query diagnostics” feature.
Furthermore, the query must not start with your own SQL code. Otherwise, no query step will be folded.
How do I use Query Folding in Power Query?
To perform query folding in Power BI, follow these steps:
- Connect to a Power BI data source that supports query folding. To do this, click on “Get data” and choose the appropriate data source.
- Load the data before making any modifications.
- Click on “Transform data”, and you’ll be redirected to the Power Query editor. You can, for example, delete columns, add rows, sort the rows of a column in ascending order, etc.
- Return to the “Home” tab and click on “Close and Apply” to load the data into Power BI.
The process can then be repeated for all the columns to which you wish to apply Query Folding in Power Query.
While query folding is essential for data modeling, it is only one of many queries. To manage huge volumes of data efficiently, it’s best to get trained with DataScientest. Find out more about our training courses.
Things to remember about Query Folding in Power Query:
- By generating a single instruction to transform data sets, query folding simplifies the processing of large volumes of data.
- However, it cannot be used for all queries. This is because the sources and transformations requested must support query folding. Above all, the query must be foldable.
- If all these conditions are met, you’ll be able to transform large datasets from within the Power Query editor.
