Following on from my last post on checking whether query folding is taking place in a Power BI/Power Query query, I’ve just stumbled on another way of doing this – by looking at metadata. I blogged about M metadata and Power Query some time ago (you might want to read the post to get some background), but it seems like metadata is used more widely than I thought in M…
Take the following query, as used in my previous blog post:
[sourcecode language=”text”]
let
Source = Sql.Database("localhost", "adventure works dw"),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
#"Removed Other Columns" =
Table.SelectColumns(
dbo_DimDate,
{"DateKey", "FullDateAlternateKey",
"DayNumberOfWeek"}),
#"Filtered Rows" =
Table.SelectRows(
#"Removed Other Columns",
each ([DayNumberOfWeek] = 1))
in
#"Filtered Rows"
[/sourcecode]
As shown in that post this query connects to the Adventure Works DW database in SQL Server and gets a few columns plus some filtered rows from the DimDate table, and most importantly query folding takes place for all of these transformations.
It turns out that each of the variables used as steps in the query can have metadata automatically assigned to them, some of which is relevant to query folding, and we can use the Value.Metadata() function to get this metadata. So, taking the previous query, we can add two extra steps:
[sourcecode language=”text” highlight=”13,14″ padlinenumbers=”true”]
let
Source = Sql.Database("localhost", "adventure works dw"),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
#"Removed Other Columns" =
Table.SelectColumns(
dbo_DimDate,
{"DateKey", "FullDateAlternateKey",
"DayNumberOfWeek"}),
#"Filtered Rows" =
Table.SelectRows(
#"Removed Other Columns",
each ([DayNumberOfWeek] = 1)),
GetMetadata = Value.Metadata(#"Filtered Rows"),
QueryFolding = GetMetadata[QueryFolding]
in
QueryFolding
[/sourcecode]
The GetMetadata step gets the metadata associated with the #”Filtered Rows” step, and that metadata comes back in the form of a record with a single field called QueryFolding. The next step gets the data from that field, which is again in the form of a record:
So we can see quite clearly that this step is being folded and that SQL is being generated in the background. It doesn’t seem to be possible to get the SQL query generated here though.
One last thing to note: while all of this works for SQL Server, when I tested it on an SSAS data source where query folding is also definitely taking place the metadata told me – incorrectly – that there was no query folding happening (I couldn’t see the MDX query generated using the View Native Query menu option either). Maybe this is all still a work in progress?
Great post Chris! Very Helpful.
Thanks!
Thanks Chris,
I’m getting an error when I try to incorporate this into my code:
Expression.Error: The field ‘QueryFolding’ of the record wasn’t found.
Details:
Do you know what I might be doing wrong?
Thanks
(using Excel 2016)
Are you using the Office 365 click-to-run version of Excel? If not, your version of Power Query probably won’t include this feature.
We’re not on 365 yet, that’ll be it then. I’ll replicate my query in Power BI and try it there.
Cheers
I tried it with Excel 2016 (on prem) as well and getting the same result as Richard. However in Query Editor I get a properly looking table as in Chris’s screenshot above. Strange thing is: doesn’t matter how simple the query I use is, it always says “IsFolded: False”. Tried it with Microsoft Dynamics CRM 2016 On Premises and with MS Dynamics 365 Online.
Hi Chris
Quick question: I note that Table.Profile() does query fold in most cases (using SQL Profiler) however it does not show up in either: (a) right click step -> View Native Query; and (b) GetMetadata. Do you know what’s happening here?
Cheers,
Simon
There seem to be a few scenarios where View Native Query doesn’t show a query, even when query folding is taking place (eg when you are importing data from SSAS), so I guess it’s another one of those. I think the Power Query dev team are working on UI improvements that will show us what is folding and what isn’t.
Hi Chris. Have you succeed using query folding against Exchange Online? Every step in the query I have tried doesn’t allow query folding.
For some data sources (and Exchange might be the same) there is no way of knowing whether query folding is taking place in the user interface.
Thank you Chris
Hi Chris,
I was trying to check if “query folding” is happening using “value.metadata” function as suggested. But I did not get any result (Expression.Error: The field ‘QueryFolding’ of the record wasn’t found) when I tried this in original file. But when I tried to recreate the same transformations in a new file, I could see the query folding details ISFOLDED = TRUE, HASNATIVEQUERY = FALSE as shown in the screenshots. Do you know why “value.metadata” does not return any value in some scenarios?
Source – DataBricks
Thanks in advance,
Sneha
Hi Chris,
I was trying to check if “query folding” is happening using “value.metadata” function. But I did not get any result (Expression.Error: The field ‘QueryFolding’ of the record wasn’t found) when I tried this in original pbix file. But when I tried to recreate the same transformations in a new file, I could see the query folding details as shown (ISFOLDED = TRUE, HASNATIVEQUERY = FALSE). Do you know why “value.metadata” does not return any value in some scenarios?
Source: Databricks
Thanks in advance,
Sneha
Hi Chris, i tried it and it query folding is indeed happening, although in the power query step itself it is greyed out. But what does that mean for my dataset and the refresh? and how can i turn it off?