Function To Audit Datasources For Query Folding
Functions to quickly check which of the data sources are folding
- Query Folding
- Query Folding Audit
- Function 1: __QFIndicator
- Function 2: __QFAudit
I will assume you already know what is query folding. If you don't, please read this documentation or watch the session I presented few months ago. I have included more resources at the bottom.
Query Folding Audit
Imagine a scenario where you are revisiting one of your old reports or are helping a client optimize their dataset performance. One of the first things you will look at are which of the queries are folding. If the query is folding, Power BI will offload all or part of the transformations back to the datasource thus resulting in faster refreshes. If you just have couple of data sources, you can check it by right clicking or using other methods I described in the video above. But if you have 20-30 queries, which is not uncommon, it will take time. What if there was a quick way so you can prioritize which queries to check ? The functions I wrote below will do exactly that.
The function relies on two M functions.
Value.Metadata which checks if the query is folding. And using
#section to get list of all the queries. You can read more about
Value.Metadata on Chris Webb's blog here.
Function 1: __QFIndicator
This function uses
Value.Metadata to check if query is folding or is foldable. If it returns
FALSE, either the datasource is not foldable (such as Excel, CSV etc.) or the query is not folding.
FALSE. BigQuery, OData are some examples of that.
// Save as __QFIndicator (tableName as table) => let fn = if (Value.Metadata(tableName)[QueryFolding][IsFolded] = true) then "Query Folded Successfully" else error Error.Record( "Query Folding Warning", "Query not folding", "Either the query does not use native query (i.e SQL) or the transformations are not foldable. If you are using SQL, check the query logs" ) in fn
// Save as __QFAudit (optional RunFn as text) => let Source = #sections[Section1], #"Converted to Table" = Record.ToTable(Source), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Converted to Table"), #"Added Custom" = Table.AddColumn( #"Removed Errors", "IsTable", each [Value] is table, type logical ), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([IsTable] = true)), QFCheck = Table.AddColumn( #"Filtered Rows", "QF Check", each try __QFIndicator([Value]) otherwise Character.FromNumber(10060) & " Check the query", type text ), Result = Table.Sort(QFCheck, QF Check) in Result
- Just save these two functions in PowerQuery
- It will return a table that looks like below.
- Note that the table doesn't say query is not folding. It's prompting you to check the query. In some cases your data source may not support folding and in other query could be folding but
Value.Metadatamay not be able to pick it up. That's just the limitation of the function. You will get the same results in Dataflow where Query Folding indicator is available.
#sectionare not available in modeling fields and it won’t refresh automatically. This is a helper function. You can read more about it here.
- My session on Query Folding : https://player.vimeo.com/video/714177719?h=466f2d1c1b
- Chris Webb's Blog : https://blog.crossjoin.co.uk/2016/08/02/another-way-to-check-query-folding-in-power-bipower-query-m-code/
- 30 Day Query Folding Challenge : https://www.youtube.com/watch?v=9sV3hIn8VTY
- Nikola's blog: https://data-mozart.com/query-folding-devil-is-in-the-detail/
- Query Folding Guidance: https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding