Forum Discussion
Super slow query after filter
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
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
Hi Daniel,
Thanks so much for such a detailed reply. I tried the code and got an error that said "Expression.SyntaxError: Token ',' expected." I had to remove two of the brackets in your code (just after the " units"}))) so I'm not sure what the issue is. If you have a minute, would you be so kind as to troubleshoot your code you provided and let me know how to modify? Again, thanks so much for your help.
Jeremy
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
OMG!!! Daniel, you are the greatest! Works perfectly and the refresh was maybe 20 seconds instead of 8 hours. Thank you thank you thank you.