SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel

Edit: There is currently no interest at Microsoft to change the current behaviour: http://community.powerbi.com/t5/Issues/Bug-Query-folding-not-working-with-non-SQL-datasource/idi-p/225100#M11611 .

I found that this workaround works also pretty well (faster in most cases), as long as your filter-tables are not too long: http://community.powerbi.com/t5/Desktop/Parameterized-SQL-Query-with-query-folding/td-p/171503

Are your SQL queries that filter with a non-SQL-table slow in PowerBI and PowerQuery in Excel? Then this might be of interest for you:

Nearly 2 years ago when I published my first blogpost about the bug that prevents query folding on SQL-sources when filtered by non-SQL-sources, PowerBI was still so new that I even didn’t mention it in my blogpost. I had enormous problems with some clients work with performance and spent 2-digit hours on figuring out the reason with some good help from the TechNet-Forum.

Today I was notified that “this bug was deemed as not high-pri enough to warrant a fix”. So it’s well alive and sucking: In Excel as well as in PowerBI. Considering how many people read my article, I found it worth to mention again and provide an improved solution. It is a function that’s easy to implement and takes the name of the tables and its key-columns as parameters:

Code to fix Query-Folding bug in PowerBi and PowerQuery

Code to fix Query Folding Bug in PowerBI and PowerQuery

And the code to copy: FilterSQLTable.txt

Hope this prevents you from wasting precious time.

Comments (12) Write a comment

  1. Pingback: #Excel Super Links #103 – shared by David Hager | Excel For You

  2. How do I apply this code to my query? Should this be placed at the top before the LET statement?

    Reply

    • Hi Jason,
      this is a function that you should copy into a separate query (open the advanced editor and replace the existing code with it).
      /Imke

      Reply

      • Thank you for your response. Once I have this saved as a separate query, how do I apply this function to my filter query? Below are the steps in my query:
        let
        Source = Oracle.Database(“prod”, [HierarchicalNavigation=true]),
        PENTA = Source{[Schema=”PENTA”]}[Data],
        JOB_LAB_RATE_SCHD1 = PENTA{[Name=”JOB_LAB_RATE_SCHD”]}[Data],
        #”Removed Other Columns” = Table.SelectColumns(JOB_LAB_RATE_SCHD1,{“JOB_ID”, “PR_CLASS_NUM”, “EFF_DATE”, “REG_RATE”, “TH_RATE”, “DT_RATE”, “LOW_PAY_RT_NUM”, “HIGH_PAY_RT_NUM”}),
        #”Filter on JOB ID FILTER” = Table.SelectRows(#”Removed Other Columns”, each List.Contains(JOB_ID_FILTER_TBL,[JOB_ID]))
        in
        #”Filter on JOB ID FILTER”

        Reply

        • Hi Jason,
          you have to transform your filter list (“JOB_ID_FILTER_TBL”) into a table. Name the column “Column1” and your function “TheFunction”, then your code would be this:

          let
          Source = Oracle.Database(“prod”, [HierarchicalNavigation=true]),
          PENTA = Source{[Schema=”PENTA”]}[Data],
          JOB_LAB_RATE_SCHD1 = PENTA{[Name=”JOB_LAB_RATE_SCHD”]}[Data],
          #”Removed Other Columns” = Table.SelectColumns(JOB_LAB_RATE_SCHD1,{“JOB_ID”, “PR_CLASS_NUM”, “EFF_DATE”, “REG_RATE”, “TH_RATE”, “DT_RATE”, “LOW_PAY_RT_NUM”, “HIGH_PAY_RT_NUM”}),
          #”Filter on JOB ID FILTER” = TheFunction(#”Removed Other Columns”, "JOB_ID", JOB_ID_FILTER_TBL, "Column1")
          in
          #”Filter on JOB ID FILTER”

          Reply

  3. I copied the function txt as you provided and pasted it into a new query then saved the query/function as Thefunction. I changed the column heading to Column1 in the JOB ID FILTER. I also update my query as you show above. The query runs, however it is showing null for each column of data. Any thoughts? Your assistance is greatly appreciated.

    JOB_ID_FILTER_TABLE:
    let
    Source = Excel.CurrentWorkbook(){[Name=”PARENT_JOB_IDS”]}[Content],
    #”Filtered Rows” = Table.SelectRows(Source, each [FILTERED] > 0),
    #”Merged Queries” = Table.NestedJoin(#”Filtered Rows”,{“PAR_JOB_ID”},BI45_JOB_MASTER,{“PAR_JOB_ID”},”BI45_JOB_MASTER”,JoinKind.LeftOuter),
    #”Expanded BI45_JOB_MASTER” = Table.ExpandTableColumn(#”Merged Queries”, “BI45_JOB_MASTER”, {“JOB_CUS_NAME”, “JOB_JOB_ID”, “JOBID NAME”}, {“JOB_CUS_NAME”, “JOB_JOB_ID”, “JOBID NAME”}),
    #”Renamed Columns” = Table.RenameColumns(#”Expanded BI45_JOB_MASTER”,{{“JOB_JOB_ID”, “Column1″}}),
    #”Filtered Rows1″ = Table.SelectRows(#”Renamed Columns”, each ([Column1] = “51L18-0002.001″))
    in
    #”Filtered Rows1”

    LABOR RATE QUERY:
    let
    Source = Oracle.Database(“prod”, [HierarchicalNavigation=true]),
    PENTA = Source{[Schema=”PENTA”]}[Data],
    JOB_LAB_RATE_SCHD1 = PENTA{[Name=”JOB_LAB_RATE_SCHD”]}[Data],
    #”Removed Other Columns” = Table.SelectColumns(JOB_LAB_RATE_SCHD1,{“JOB_ID”, “PR_CLASS_NUM”, “EFF_DATE”, “REG_RATE”, “TH_RATE”, “DT_RATE”, “LOW_PAY_RT_NUM”, “HIGH_PAY_RT_NUM”}),
    #”Filter on JOB ID FILTER” = TheFunction(#”Removed Other Columns”, “JOB_ID”, JOB_ID_FILTER_TBL, “Column1″)
    in
    #”Filter on JOB ID FILTER”

    TheFunction:
    (SQLTable as table, keysql as text, filtertable as table, keyfilter as text) =>

    let
    // 1 Splitting filter table into chuncks of 200 lines and add index
    Quelle = filtertable ,
    AddIndex = Table.AddIndexColumn(Quelle, “Index”, 1, 1),
    FilterTable = Table.AddColumn(AddIndex, “Group”, each Number.RoundUp([Index]/200)),

    // 2 Performing the filter operation in a function
    function = (group)=>
    let
    Source = SQLTable,
    Result = Table.Buffer(Table.NestedJoin(Source,{keysql},Table.SelectRows(FilterTable, each [Group] = group),{keyfilter},”NewColumn”,JoinKind.Inner))
    in
    Result,

    // 3 Main query: Create table with groups to call function from, call function and expand (thereby stitching the results back together)
    Source = {1..Number.RoundUp(List.Max(FilterTable[Group]))},
    ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AddFunctionCol = Table.AddColumn(ConvertToTable, “Custom”, each function([Column1])),
    Remove = Table.RemoveColumns(AddFunctionCol ,{“Column1″}),
    #”Expanded Custom” = Table.ExpandTableColumn(Remove, “Custom”, Table.ColumnNames(Remove[Custom]{0}))
    in
    #”Expanded Custom”

    Reply

    • Hi Jason,
      are you aware that you’ve filtered your JOB_ID_FILTER_TABLE down to just one (distinct) value (last step: #”Filtered Rows1″)?
      Otherwise, please change the last row of the function from “#”Expanded Custom”” to “AddFunctionCol” to see the returned table before expansion and try to expand manually.
      /Imke

      Reply

  4. This is great! Thank you! Have you tried something similar for doing a group by operation? I’m trying to do a distinct count and it’s not folding either. For now, just trying to adjust part 2 of the function for this.

    Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz