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.