Query folding on native queries

  • Article

In Power Query, you can define a native query and run it against your data source. The Import data from a database using native database query article explains how to do this process with multiple data sources. But, by using the process described in that article, your query doesn't take advantage of any query folding from subsequent query steps.

This article showcases an alternative method to create native queries against your data source using the Value.NativeQuery function and keep the query folding mechanism active for subsequent steps of your query.

Note

We recommend that you read the documentation on query folding and the query folding indicators to better understand the concepts used throughout this article.

Supported data connectors

The method described in the next sections applies to the following data connectors:

Connect to target from data source

Note

To showcase this process, this article uses the SQL Server connector and the AdventureWorks2019 sample database. The experience might vary from connector to connector, but this article showcases the fundamentals on how to enable query folding capabilities over native queries for the supported connectors.

When connecting to the data source, it's important that you connect to the node or level where you want to execute your native query. For the example in this article, that node is the database level inside the server.

Screenshot of the connection settings dialog for the connection to the AdventureWorks2019 database on a local instance of SQL Server.

After you define the connection settings and supplying the credentials for your connection, the navigation dialog for your data source opens. The navigation dialog contains all the available objects that you can connect to.

From this list, you need to select the object where the native query is run (also known as the target). For this example, that object is the database level.

At the navigator window in Power Query, select and hold (or right-click) the database node in the navigator window and select the Transform Data option. Selecting this option creates a new query of the overall view of your database, which is the target you need to run your native query.

Screenshot of choose data where the user right clicked the database node in the navigator, with Transform Data emphasized.

Once your query lands in the Power Query editor, only the Source step should show in the Applied steps pane. This step contains a table with all the available objects in your database, similar to how they were displayed in the Navigator window.

Screenshot of the query with only the source step.

Use Value.NativeQuery function

The goal of this process is to execute the following SQL code, and to apply more transformations with Power Query that can be folded back to the source.

SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'

The first step was to define the correct target, which in this case is the database where the SQL code is run. Once a step has the correct target, you can select that step—in this case, Source in Applied Steps—and then select the fx button in the formula bar to add a custom step. In this example, replace the Source formula with the following formula:

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'  ", null, [EnableFolding = true])

The most important component of this formula is the use of the optional record for the fourth parameter of the function that has the EnableFolding record field set to true.

Screenshot of the new custom step formula with the usage of the Value.NativeQuery function and the explicit SQL query.

Note

You can read more about the Value.NativeQuery function from the official documentation article.

After you enter the formula, a warning is shown that requires you to enable native queries to run for your specific step. Select continue for this step to be evaluated.

This SQL statement yields a table with only three rows and two columns.

Screenshot with the results of the native query evaluated against the target database.

Test query folding

To test the query folding of your query, you can try to apply a filter to any of your columns and see if the query folding indicator in the applied steps section shows the step as folded. For this case, you can filter the DepartmentID column to have values that aren't equal to two.

Screenshot demonstrating how to filter the DepartmentID column to only have the values that aren't equal to two.

After adding this filter, you can check that the query folding indicators still show the query folding happening at this new step.

Filter step shown as folded back to the data source in the applied steps section.

To further validate what query is being sent to the data source, you can select and hold (or right-click) the Filtered rows step and select the View query plan option to check the query plan for that step.

In the query plan view, you can see that a node with the name Value.NativeQuery that has a View Details hyperlink. You can select this hyperlink to view the exact query that's being sent to the SQL Server database.

The native query is wrapped around another SELECT statement to create a subquery of the original. Power Query does its best to create the most optimal query given the transforms used and the native query provided.

Screenshot of the query plan for the Filtered rows step.

Tip

For scenarios where you get errors because query folding wasn't possible, we recommend that you try validating your steps as a subquery of your original native query to check if there might be any syntax or context conflicts.