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

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

according your code, think you've walked through the data too many times, you can try to do all the steps in Table.Group()

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(_), a[#"Location-Warehouse"]{x-1}&" ("& Number.ToText(a[Units]{x-1})&" units)"})))})[n])

in

   Custom1