Query folding examples

This article provides some example scenarios for each of the three possible outcomes for query folding. It also includes some suggestions on how to get the most out of the query folding mechanism, and the effect that it can have in your queries.

The scenario

Imagine a scenario where, using the Wide World Importers database for Azure Synapse Analytics SQL database, you're tasked with creating a query in Power Query that connects to the fact_Sale table and retrieves the last 10 sales with only the following fields:

• Sale Key
• Customer Key
• Invoice Date Key
• Description
• Quantity

This article showcases three ways to achieve the same output with different levels of query folding:

• No query folding
• Partial query folding
• Full query folding

No query folding example

After connecting to your database and navigating to the fact_Sale table, you select the Keep bottom rows transform found inside the Reduce rows group of the Home tab.

After selecting this transform, a new dialog appears. In this new dialog, you can enter the number of rows that you'd like to keep. For this case, enter the value 10, and then select OK.

Next, select the Choose columns transform found inside the Manage columns group of the Home tab. You can then select the columns you want to keep from your table and remove the rest.

Lastly, inside the Choose columns dialog, select the Sale Key, Customer Key, Invoice Date Key, Description, and Quantity columns, and then select OK.

The following code sample is the full M script for the query you created:

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Kept bottom rows" = Table.LastN(Navigation, 10),
  #"Choose columns" = Table.SelectColumns(#"Kept bottom rows", {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"})
in
  #"Choose columns""

No query folding: Understanding the query evaluation

Under Applied steps in the Power Query editor, you’ll notice that the query folding indicators for Kept bottom rows and Choose columns are marked as steps that will be evaluated outside the data source or, in other words, by the Power Query engine.

You can right-click the last step of your query, the one named Choose columns, and select the option that reads View Query plan. The goal of the query plan is to provide you with a detailed view of how your query is run. To learn more about this feature, go to Query plan.

Each box in the previous image is called a node. A node represents the operation breakdown to fulfill this query. Nodes that represent data sources, such as SQL Server in the example above and the Value.NativeQuery node, represent which part of the query is offloaded to the data source. The rest of the nodes, in this case Table.LastN and Table.SelectColumns highlighted in the rectangle in the previous image, are evaluated by the Power Query engine. These two nodes represent the two transforms that you added, Kept bottom rows and Choose columns. The rest of the nodes represent operations that happen at your data source level.

To see the exact request that is sent to your data source, select View details in the Value.NativeQuery node.

This data source request is in the native language of your data source. For this case, that language is SQL and this statement represents a request for all the rows and fields from the fact_Sale table.

Consulting this data source request can help you better understand the story that the query plan tries to convey:

• Sql.Database: This node represents the data source access. Connects to the database and sends metadata requests to understand its capabilities.
• Value.NativeQuery: Represents the request that was generated by Power Query to fulfill the query. Power Query submits the data requests in a native SQL statement to the data source. In this case, that represents all records and fields (columns) from the fact_Sale table. For this scenario, this case is undesirable, as the table contains millions of rows and the interest is only in the last 10.
• Table.LastN: Once Power Query receives all records from the fact_Sale table, it uses the Power Query engine to filter the table and keep only the last 10 rows.
• Table.SelectColumns: Power Query will use the output of the Table.LastN node and apply a new transform called Table.SelectColumns, which selects the specific columns that you want to keep from a table.

For its evaluation, this query had to download all rows and fields from the fact_Sale table. This query took an average of 6 minutes and 1 second to be processed in a standard instance of Power BI dataflows (which accounts for the evaluation and loading of data to dataflows).

Partial query folding example

After connecting to the database and navigating to the fact_Sale table, you start by selecting the columns that you want to keep from your table. Select the Choose columns transform found inside the Manage columns group from the Home tab. This transform helps you to explicitly select the columns that you want to keep from your table and remove the rest.

Inside the Choose columns dialog, select the Sale Key, Customer Key, Invoice Date Key, Description, and Quantity columns and then select OK.

You now create logic that will sort the table to have the last sales at the bottom of the table. Select the Sale Key column, which is the primary key and incremental sequence or index of the table. Sort the table using only this field in ascending order from the context menu for the column.

Next, select the table contextual menu and choose the Keep bottom rows transform.

In Keep bottom rows, enter the value 10, and then select OK.

The following code sample is the full M script for the query you created:

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Choose columns" = Table.SelectColumns(Navigation, {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}),
  #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
  #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
  #"Kept bottom rows"

Partial query folding example: Understanding the query evaluation

Checking the applied steps pane, you notice that the query folding indicators are showing that the last transform that you added, Kept bottom rows, is marked as a step that will be evaluated outside the data source or, in other words, by the Power Query engine.

You can right-click the last step of your query, the one named Kept bottom rows, and select the Query plan option to better understand how your query might be evaluated.

Each box in the previous image is called a node. A node represents every process that needs to happen (from left to right) in order for your query to be evaluated. Some of these nodes can be evaluated at your data source while others, like the node for Table.LastN, represented by the Kept bottom rows step, are evaluated using the Power Query engine.

To see the exact request that is sent to your data source, select View details in the Value.NativeQuery node.

This request is in the native language of your data source. For this case, that language is SQL and this statement represents a request for all the rows, with only the requested fields from the fact_Sale table ordered by the Sale Key field.

Consulting this data source request can help you better understand the story that the full query plan tries to convey. The order of the nodes is a sequential process that starts by requesting the data from your data source:

• Sql.Database: Connects to the database and sends metadata requests to understand its capabilities.
• Value.NativeQuery: Represents the request that was generated by Power Query to fulfill the query. Power Query submits the data requests in a native SQL statement to the data source. For this case, that represents all records, with only the requested fields from the fact_Sale table in the database sorted in ascending order by the Sales Key field.
• Table.LastN: Once Power Query receives all records from the fact_Sale table, it uses the Power Query engine to filter the table and keep only the last 10 rows.

For its evaluation, this query had to download all rows and only the required fields from the fact_Sale table. It took an average of 3 minutes and 4 seconds to be processed in a standard instance of Power BI dataflows (which accounts for the evaluation and loading of data to dataflows).

Full query folding example

After connecting to the database and navigating to the fact_Sale table, start by selecting the columns that you want to keep from your table. Select the Choose columns transform found inside the Manage columns group from the Home tab. This transform helps you to explicitly select the columns that you want to keep from your table and remove the rest.

In Choose columns, select the Sale Key, Customer Key, Invoice Date Key, Description, and Quantity columns, and then select OK.

You now create logic that will sort the table to have the last sales at the top of the table. Select the Sale Key column, which is the primary key and incremental sequence or index of the table. Sort the table only using this field in descending order from the context menu for the column.

Next, select the table contextual menu and choose the Keep top rows transform.

In Keep top rows, enter the value 10, and then select OK.

The following code sample is the full M script for the query you created:

let
  Source = Sql.Database(ServerName, DatabaseName),
  Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
  #"Choose columns" = Table.SelectColumns(Navigation, {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}),
  #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
  #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
  #"Kept top rows"

Full query folding example: Understanding the query evaluation

When checking the applied steps pane, you'll notice that the query folding indicators are showing that the transforms that you added, Choose columns, Sorted rows, and Kept top rows, are marked as steps that will be evaluated at the data source.

You can right-click the last step of your query, the one named Kept top rows, and select the option that reads Query plan.

This request is in the native language of your data source. For this case, that language is SQL and this statement represents a request for all the rows and fields from the fact_Sale table.

Consulting this data source query can help you better understand the story that the full query plan tries to convey:

• Sql.Database: Connects to the database and sends metadata requests to understand its capabilities.
• Value.NativeQuery: Represents the request that was generated by Power Query to fulfill the query. Power Query submits the data requests in a native SQL statement to the data source. For this case, that represents a request for only the top 10 records of the fact_Sale table, with only the required fields after being sorted in descending order using the Sale Key field.

For its evaluation, this query only downloads 10 rows, with only the fields that you requested from the fact_Sale table. This query took an average of 31 seconds to be processed in a standard instance of Power BI dataflows (which accounts for the evaluation and loading of data to dataflows).

Performance comparison

To better understand the affect that query folding has in these queries, you can refresh your queries, record the time it takes to fully refresh each query, and compare them. For simplicity, this article provides the average refresh timings captured using the Power BI dataflows refresh mechanic while connecting to a dedicated Azure Synapse Analytics environment with DW2000c as the service level.

The refresh time for each query was as follows:

It's often the case that a query that fully folds back to the data source outperforms similar queries that don't completely fold back to the data source. There could be many reasons why this is the case. These reasons range from the complexity of the transforms that your query performs, to the query optimizations implemented at your data source, such as indexes and dedicated computing, and network resources. Still, there are two specific key processes that query folding tries to use that minimizes the affect that both of these processes have with Power Query:

• Data in transit
• Transforms executed by the Power Query engine

The following sections explain the affect that these two processes have in the previously mentioned queries.

Data in transit

When a query gets executed, it tries to fetch the data from the data source as one of its first steps. What data is fetched from the data source is defined by the query folding mechanism. This mechanism identifies the steps from the query that can be offloaded to the data source.

The following table lists the number of rows requested from the fact_Sale table of the database. The table also includes a brief description of the SQL statement sent to request such data from the data source.

When requesting data from a data source, the data source needs to compute the results for the request and then send the data to the requestor. While the computing resources have already been mentioned, the network resources of moving the data from the data source to Power Query, and then have Power Query be able to effectively receive the data and prepare it for the transforms that will happen locally can take some time depending on the size of the data.

For the showcased examples, Power Query had to request over 3.6 million rows from the data source for the no query folding and partial query folding examples. For the full query folding example, it only requested 10 rows. For the fields requested, the no query folding example requested all the available fields from the table. Both the partial query folding and the full query folding examples only submitted a request for exactly the fields that they needed.

Transforms executed by the Power Query engine

This article showcased how you can use the Query plan to better understand how your query might be evaluated. Inside the query plan, you can see the exact nodes of the transform operations that will be performed by the Power Query engine.

The following table showcases the nodes from the query plans of the previous queries that would have been evaluated by the Power Query engine.

For the examples showcased in this article, the full query folding example doesn't require any transforms to happen inside the Power Query engine as the required output table comes directly from the data source. In contrast, the other two queries required some computation to happen at the Power Query engine. Because of the amount of data that needs to be processed by these two queries, the process for these examples takes more time than the full query folding example.

Transforms can be grouped into the following categories:

Considerations and suggestions

• Follow the best practices when creating a new query, as stated in Best practices in Power Query.
• Use the query folding indicators to check which steps are preventing your query from folding. Reorder them if necessary to increase folding.
• Use the query plan to determine which transforms are happening at the Power Query engine for a particular step. Consider modifying your existing query by re-arranging your steps. Then check the query plan of the last step of your query again and see if the query plan looks better than the previous one. For example, the new query plan has less nodes than the previous one, and most of the nodes are “Streaming” nodes and not “full scan”. For data sources that support folding, any nodes in the query plan other than Value.NativeQuery and data source access nodes represent transforms that didn’t fold.
• When available, you can use the View Native Query (or View data source query) option to ensure that your query can be folded back to the data source. If this option is disabled for your step, and you're using a source that normally enables it, you've created a step that stops query folding. If you're using a source that doesn't support this option, you can rely on the query folding indicators and query plan.
• Use the query diagnostics tools to better understand the requests being sent to your data source when query folding capabilities are available for the connector.
• When combining data sourced from the use of multiple connectors, Power Query tries to push as much work as possible to both of the data sources while complying with the privacy levels defined for each data source.
• Read the article on privacy levels to protect your queries from running against a Data Privacy Firewall error.
• Use other tools to check query folding from the perspective of the request being received by the data source. Based on the example in this article, you can use the Microsoft SQL Server Profiler to check the requests being sent by Power Query and received by the Microsoft SQL Server.
• If you add a new step to a fully folded query and the new step also folds, Power Query might send a new request to the data source instead of using a cached version of the previous result. In practice, this process can result in seemingly simple operations on a small amount of data taking longer to refresh in the preview than expected. This longer refresh is due to Power Query requerying the data source rather than working off a local copy of the data.