When we use Power Query or Dataflows to import and transform data, we can often hit a proverbial ‘wall’ when a query (or it’s preview) starts to run VERY SLOW. Perhaps you’ve experienced this after stacking 10 merge columns in a single query and then having to wait for over an hour every time a changed is made in a complex or large Power BI project.

If you haven’t heard by now, there is a better way.

Query Folding to the rescue:

According to Microsoft, when a data source supports the concept of a query language, Power BI will attempt to use ‘fold’ the transformation work back to this source system in the form of a single SELECT statement. The benefit to this happening is that Power BI uses the processing power of the source system instead of it’s own Vertipaq engine.

As you can see below – this process largely happens automatically in Power BI and can be confirmed for most data sources when the “View Native Query” option is enabled in Desktop or when a green icon is visible in a Dataflow.

The benefits of getting the source system involved can be demonstrated by comparing the refresh timing of the sample query pictured above simply by shifting the location of the “Changed Type” step which does not fold.

Keep in mind that these are the EXACT SAME query – it’s just the order of one step that has been changed.

This may seem minor, but the benefits of query folding in reducing refresh time grow exponentially as the size of the data model (and work) grows.

So it’s important that we try to maintain query folding for as long as possible, but paying particular attention to what actions ‘break’ folding (and shifting them to the end).

Using Advanced Options Breaks Folding

It’s not uncommon for SQL programmers entering the Power BI landscape to ‘re-use’ SQL statements during the import step of a Power BI report build.

This may make sense if the logic has already been borne out in SQL – why re-invent the wheel in Power Query. For example, this query returns that same table as our Power Query steps from earlier.

Unfortunately – although the SQL returns the expected result – the use of this area in Power BI – disables query folding for all subsequent steps even if those steps could be easily integrated into a SQL statement where clause.

When we look at the single refresh results of this query against the previous two, we can see that it still performs reasonably well (despite disabling query folding for all subsequent steps).

‘Trick’ Power Query to Fold with Value.NativeQuery()

This simple example may not make the case for why a developer should stop using SQL in the Advanced Options in favor of Power Query M code, but consider how a non-folding query will impact refresh length as additional steps are added. There can always be a case for seeking out folding whenever possible.

There is a way to make Power BI ‘fold’ our SQL statement just through a rearrangement of our query. Here’s how.

Here are two queries opened in the Power Query Advanced Editor. Both use the same SQL statement and return the exact same table, but the bottom option will still fold.

Let’s break down the structure of the bottom query. Here’s a simplified version of the code – with the main elements that need to be entered in BOLD.

let
    Source = Sql.Databases("SERVERNAME"),
    CallDB = Source{[Name="DATABASENAME"]}[Data],
    UseNativeQuery = Value.NativeQuery(CallDB,"SQL STATEMENT",null,[EnableFolding=true])
in
   UseNativeQuery

The key elements are:

  • Removing all SQL from the SQL Statement part of the source import step and pasting it in the Value.NativeQuery() expression.
  • Copy the code elements explicitly from the code block above. (There is an ‘s’ in sql.databases(), and also important to include the null and EnabledFolding=True elements for this to work.)

Once this has been setup correctly – query folding will now be enabled (and presumably continue) if we keep adding foldable steps.

This strategy has limits

There is a natural limit to what elements of SQL queries can be ‘folded’ in this way.

VIEW Tables – Yes

Stored Procedures – No

Complex SQL Queries – Depends, but probably not.

The query below works fine when entered in the SQL Statement part of the Power BI import step – but cannot be folded when used in the Value.NativeQuery pattern.

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END;

CREATE Table #Temp (
	[Date_of_Sale] datetime2(7) NOT NULL,
	[Category_Name] nvarchar(50) NOT NULL,
	[State] nvarchar(50) NOT NULL,
	[Store_Identifier] nvarchar(50) NOT NULL,
	[vendor_name] nvarchar(50) NOT NULL,
	[Item_Number] int NOT NULL,
	[Qty] int NOT NULL,
	[Sale_Dollars] float NOT NULL);

INSERT INTO #Temp EXEC dbo.NWSales_as_StoredProcedure;

SELECT * FROM #Temp;

DROP TABLE #Temp;

Certainly this is not an exhaustive list of all the possible sources that can enable folding – but the pattern is certainly repeatable if there are additional sources we’d like to test.

Until then – happy folding!