Forum Discussion

jemsongs's avatar
jemsongs
Regular Visitor
3 years ago
Solved

Super slow query after filter

I have a query that works as expected up until I filter near the end and then it can take 8 hours to refresh! My code is below and the issue seems to be with the bolded code below. Any ideas would be...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    let

    Source = #"Stock Locator Inquiry",

    Custom1=Table.FromRecords(Table.Group(Source,"Style-Color",{"n",each let a=Table.Sort(_,{"Units",1}) in Record.FromTable(#table({"Name","Value"},{{"Style-Color",[#"Style-Color"]{0}}}&List.Transform({1..3},(x)=>{"Location "&Text.From(x), a[#"Location-Warehouse"]{x-1}&" ("& Number.ToText(a[Units]{x-1})&" units)"})))})[n])

    in

    Custom1

jennratten's avatar
jennratten
Super User
3 years ago

Okay, yes, TransformColumnTypes is probably being added by Power Query at the pivot step.  I recommend you try this... remove Table.Buffer from the Filtered Rows step, then insert a new step between Filtered Rows and Pivot in which you either add a primary key for the group of columns (looks like Rank and Location-Qty) or select those two columns and then remove duplicates (which will do the same thing).  This will optimize your query prior to pivoting by creating partitions.

jemsongs's avatar
jemsongs
Regular Visitor
3 years ago

Thanks. I made the changes but wasn't sure if I understood you correctly. Is this what you meant:

 

let
Source = #"Stock Locator Inquiry",
#"Only kept Style-Color Column" = Table.SelectColumns(Source,{"Style-Color"}),
#"Removed Duplicates" = Table.Distinct(#"Only kept Style-Color Column"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", (CurrentStyle) => Table.AddIndexColumn( Table.Sort(Table.SelectRows (Source, (InnerStyle) => InnerStyle[#"Style-Color"] = CurrentStyle[#"Style-Color"]), {"Units", Order.Descending}), "Rank",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"EAN-UPC", "Style-Color-Size", "Location-Warehouse", "Bin", "Units", "Rank"}, {"EAN-UPC", "Style-Color-Size", "Location-Warehouse", "Bin", "Units", "Rank"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Location-Qty", each [#"Location-Warehouse"] &" ("& Number.ToText([Units])&" units)"),
#"Removed Other Columns2" = Table.SelectColumns(#"Added Custom1",{"Location-Qty", "Rank", "Style-Color"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns2",{"Style-Color", "Location-Qty", "Rank"}),
#"Removed Duplicates1" = Table.Distinct(#"Reordered Columns", {"Location-Qty", "Rank"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each [Rank] <= 3),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"Rank", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"Rank", type text}}, "en-CA")[Rank]), "Rank", "Location-Qty"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Location 1"}, {"2", "Location 2"}, {"3", "Location 3"}})
in
#"Renamed Columns"

 

I'm not sure if that worked as the refresh is about 5 minutes in and still going.

 

Thanks again for your help.

  • jennratten's avatar
    jennratten
    Super User
    3 years ago

    Almost, #"Removed Duplicates1" needs to be after #"Filtered Rows".

    • jemsongs's avatar
      jemsongs
      Regular Visitor
      3 years ago

      Hmmm...seems to still be crawling. Here's what I changed:

      let
      Source = #"Stock Locator Inquiry",
      #"Only kept Style-Color Column" = Table.SelectColumns(Source,{"Style-Color"}),
      #"Removed Duplicates" = Table.Distinct(#"Only kept Style-Color Column"),
      #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", (CurrentStyle) => Table.AddIndexColumn( Table.Sort(Table.SelectRows (Source, (InnerStyle) => InnerStyle[#"Style-Color"] = CurrentStyle[#"Style-Color"]), {"Units", Order.Descending}), "Rank",1,1)),
      #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"EAN-UPC", "Style-Color-Size", "Location-Warehouse", "Bin", "Units", "Rank"}, {"EAN-UPC", "Style-Color-Size", "Location-Warehouse", "Bin", "Units", "Rank"}),
      #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Location-Qty", each [#"Location-Warehouse"] &" ("& Number.ToText([Units])&" units)"),
      #"Removed Other Columns2" = Table.SelectColumns(#"Added Custom1",{"Location-Qty", "Rank", "Style-Color"}),
      #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns2",{"Style-Color", "Location-Qty", "Rank"}),
      #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each [Rank] <= 3),
      #"Removed Duplicates1" = Table.Distinct(#"Reordered Columns", {"Style-Color","Location-Qty"}),
      #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"Rank", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"Rank", type text}}, "en-CA")[Rank]), "Rank", "Location-Qty"),
      #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Location 1"}, {"2", "Location 2"}, {"3", "Location 3"}})
      in
      #"Renamed Columns"

       

      I'm pretty convinced it is the FilteredRows step that's killing it.

      • jennratten's avatar
        jennratten
        Super User
        3 years ago

        Please add Rank to the Removed Duplicates step and rerun. You can also try moving the Filtered Rows step up to just after Expanded Custom and moving Pivoted Column step to a new query and see what happens in terms of performance.  There is definitely room for optimization with this step:

         

        #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", (CurrentStyle) => Table.AddIndexColumn( Table.Sort(Table.SelectRows (Source, (InnerStyle) => InnerStyle[#"Style-Color"] = CurrentStyle[#"Style-Color"]), {"Units", Order.Descending}), "Rank",1,1)),

        Is there a particular reason why you are trying to do this with Power Query instead of DAX?  Often times DAX will do things like this more efficiently.