Query folding

The query folding within Power Query Editor helps you increase the performance of your Power BI reports. Query folding is the process by which the transformations and edits that you make in Power Query Editor are simultaneously tracked as native queries, or simple Select SQL statements, while you're actively making transformations. The reason for implementing this process is to ensure that these transformations can take place in the original data source server and don't overwhelm Power BI computing resources.

You can use Power Query to load data into Power BI. Then use Power Query Editor to transform your data, such as renaming or deleting columns, appending, parsing, filtering, or grouping your data.

Consider a scenario where you’ve renamed a few columns in the Sales data and merged a city and state column together in the “city state” format. Meanwhile, the query folding feature tracks those changes in native queries. Then, when you load your data, the transformations take place independently in the original source, this ensures that performance is optimized in Power BI.

The benefits to query folding include:

  • More efficiency in data refreshes and incremental refreshes. When you import data tables by using query folding, Power BI is better able to allocate resources and refresh the data faster because Power BI doesn't have to run through each transformation locally.

  • Automatic compatibility with DirectQuery and Dual storage modes. All DirectQuery and Dual storage mode data sources must have the back-end server processing abilities to create a direct connection, which means that query folding is an automatic capability that you can use. If all transformations can be reduced to a single Select statement, then query folding can occur.

The following scenario shows query folding in action. In this scenario, you apply a set of queries to multiple tables. After you add a new data source by using Power Query, and you're directed to the Power Query Editor, you go to the Query Settings pane and right-click the last applied step.

If the View Native Query option isn't available (not displayed in bold type), then query folding isn't possible for this step, and you'll have to work backward in the Applied Steps area until you reach the step in which View Native Query is available (displays in bold type). This process will reveal the native query that is used to transform the semantic model.

Native queries aren't possible for the following transformations:

  • Adding an index column
  • Merging and appending columns of different tables with two different sources
  • Changing the data type of a column

A good guideline to remember is that if you can translate a transformation into a Select SQL statement, which includes operators and clauses such as GROUP BY, SORT BY, WHERE, UNION ALL, and JOIN, you can use query folding.

While query folding is one option to optimize performance when retrieving, importing, and preparing data, another option is query diagnostics.


lean six sigma certification training courses malaysia


Comments

Post a Comment

Popular posts from this blog

Azure built-in roles for tables

Azure RBAC provides built-in roles for authorizing access to table data using Microsoft Entra ID and OAuth. Built-in roles that provide permissions to tables in Azure Storage include: Storage Table Data Contributor: Use to grant read/write/delete permissions to Table storage resources. Storage Table Data Reader: Use to grant read-only permissions to Table storage resources. To learn how to assign an Azure built-in role to a security principal, see Assign an Azure role for access to table data. To learn how to list Azure RBAC roles and their permissions, see List Azure role definitions. For more information about how built-in roles are defined for Azure Storage, see Understand role definitions. For information about creating Azure custom roles, see Azure custom roles. Only roles explicitly defined for data access permit a security principal to access table data. Built-in roles such as Owner, Contributor, and Storage Account Contributor permit a security principal to manage a storage acc...
Read more

Explore Dataflows Gen2 in Microsoft Fabric

  In Microsoft Fabric, you can create a Dataflow Gen2 in the Data Factory workload or Power BI workspace, or directly in the lakehouse. Since our scenario is focused on data ingestion, let's look at the  Data Factory  workload experience. Dataflows Gen2 use Power Query Online to visualize transformations.  1. Power Query ribbon Dataflows Gen2 support a wide variety of data source connectors. Common sources include cloud and on-premises relational databases, Excel or flat files, SharePoint, SalesForce, Spark, and Fabric lakehouses. Then there are numerous data transformations possible, such as: Filter and Sort rows Pivot and Unpivot Merge and Append queries Split and Conditional split Replace values and Remove duplicates Add, Rename, Reorder, or Delete columns Rank and Percentage calculator Choose Top N and Bottom N You can also create and manage data source connections, manage parameters, and configure the default data destination in this ribbon. 2. Queries pane The ...
Read more

Select and configure an appropriate method for access to Azure Blobs

Azure Storage supports using Microsoft Entra ID to authorize requests to blob data. With Microsoft Entra ID, you can use Azure role-based access control (Azure RBAC) to grant permissions to a security principal, which may be a user, group, or application service principal. The security principal is authenticated by Microsoft Entra ID to return an OAuth 2.0 token. The token can then be used to authorize a request against the Blob service. Authorization with Microsoft Entra ID is available for all general-purpose and Blob storage accounts in all public regions and national clouds. Only storage accounts created with the Azure Resource Manager deployment model support Microsoft Entra authorization. For optimal security, Microsoft recommends using Microsoft Entra ID with managed identities to authorize requests against blob, queue, and table data, whenever possible. Authorization with Microsoft Entra ID and managed identities provides superior security and ease of use over Shared Key author...
Read more