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

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.