Query Folding

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.

Value.Metadata

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.

Note: Value.Metadata function may not always work. There are scenarios where the query could be folding but it still might return 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

Function 2: __QFAudit

This function applies the above __QFIndicator function on all the queries returned by the #Section function and gives you a sorted list.

// 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

qf1

Steps

  1. Just save these two functions in PowerQuery
  2. Click Invoke on __QFAudit function
  3. It will return a table that looks like below.
  4. 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.Metadata may 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.

qf2

Note: Another limitation here is that if you add a new query, you will have to invoke the function again. The results of the #section are not available in modeling fields and it won’t refresh automatically. This is a helper function. You can read more about it here.

Resources

  1. My session on Query Folding : https://player.vimeo.com/video/714177719?h=466f2d1c1b
  2. Chris Webb's Blog : https://blog.crossjoin.co.uk/2016/08/02/another-way-to-check-query-folding-in-power-bipower-query-m-code/
  3. 30 Day Query Folding Challenge : https://www.youtube.com/watch?v=9sV3hIn8VTY
  4. Nikola's blog: https://data-mozart.com/query-folding-devil-is-in-the-detail/
  5. Query Folding Guidance: https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding