How to Describe the Query Folding in Power BI

by

Microsoft Power BI is one of the most popular data visualization and business intelligence tools. The Power BI tool is the collection of apps, data connectors, and software. This aims to provide interactive visualization and BI capabilities, which also come with drag and drop function. When it comes to price power, BI is much cheaper as compared to other BI tools in a market. Finally, you can access data from hundreds of supported on-premises and cloud-based sources, such as Dynamics 365, Azure SQL DB, Excel, Salesforce, and SharePoint. It ensures it’s always up to date with automated, incremental refreshes.

The Desktop version of Power BI enables you to develop deep, actionable insights for a broad range of scenarios.
Power Query is a part of Power BI. It is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs.

Introduction

Query Folding is a very important feature of Power Query. It allows you to extract and manipulate data from various sources. It is used when you transform the data; it is possible that those transformations are sent back to the source to improve performance in Power BI.

Today we’ll discuss query folding, how you can take advantage of it, and how to make sure query folding takes place?

Depending on the data source, Power Query can send some of the transformations to the source. This means that those transformations take place at the source before any data is sent to the Power Query. This is a big performance improvement since the Power Query has to import fewer data. This process is called query folding. For example, filtering rows can be sent back to SQL Server (in the form of a WHERE clause), but a transformation in Power Query that removes the five bottom rows cannot.

Some Queries

  • Filtering (on rows or columns)
  • Joins
  • Aggregates and GROUP BY
  • Pivot and unpivot
  • Numeric calculations
  • Simple transformations, such as UPPER

These are transformations that can be sent back to the source to improve the performance of the data, to import lesser data in the Power Query.

Relevant Sources

  • Relational sources (SQL Server, Oracle, …). They support most Power Query functionality.
  • OData sources (such as a SharePoint list for example and the Azure Marketplace)
  • Active Directory
  • Exchange
  • HDFS, Folder.Files and Folder.Contents (for basic operations on paths)

To understand that which Transformations Folds, simply right click on every step and see if the View Native Query is enabled or not. If the Native Query is enabled, the Query Folding is supported for that step, otherwise not. Also, please note that Query Folding is not supported for data sources such as web query, or CSV, or things like that. Query Folding at the moment is only supported for data stores that support a native query language. For Web, Folder, CSV… there is no native query language, so you don’t need to worry about Query Folding.

In the Following Scenarios, Query Folding Does Not Take Place:

  • When you are using an unsupported data source.
  • You are using an unsupported transform type.
  • You write your source query.
  • When accessing dataflow entities from Power BI Desktop, no query folding takes place, even if the enhanced compute engine is enabled.
  • When accessing dataflow entities from other entities in the Power BI service, no query folding takes place unless the enhanced compute engine enabled.
  • And When accessing dataflow entities from other entities in the Power BI service, query folding will take place when the enhanced compute engine is enabled, because the linked entity’s query will be executed against the cached data in SQL, rather than the underlying CDM folder.

In the Following Scenarios, Query Folding Takes Place:

  1. Either your Data Source must have its own Native Query Language.
  2. Or Your steps should be compatible with Query Folding.

The first one is easy; that is, Data Source must have its own Native Query Language. If you’re connecting to an Excel, CSV, TXT, JSON, or another type of file directly, you would not be able to take advantage of Query Folding because they do not handle their query language.

On the other hand, a SQL Database, a MySql, Oracle, Postre, or any other type of database does have their query language, and Power Query will be able to translate the query steps into a native query language for that data source.

The second one is usually difficult; that is Your steps should be compatible with Query Folding. Microsoft does not tell us what is foldable and which step is not foldable, mainly because it relies on a number of factors and variables, but there are two methods to tell if query folding is happening:

  1. Using a monitoring tool for your data source – to define what type of command you’re receiving from the Power BI/Power Query.
  2. Through Power Query – there’s a couple of ways to see if Power Query was able to come up with a specific Native Query for your source.

Conclusion

In short, query folding is the process where the M query code gets translated into the native query language of a data source.

This happens automatically. Therefore, you do not have to do anything whatsoever. As a result, Power Query will try to do its best job to optimize everything that you’ve done and “fold it” automatically.

You can visit our site for consultancy in Power BI and other services too. The link mentioned below:

https://www.loginworks.com/data_services_consulting

We hope you had a good read. If you would like to leave any feedback or post a comment, please provide it in the section below.

Latest posts by Ravi Verma (see all)

Leave a Comment