Forum Discussion
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 greatly appreciated. Thanks in advance 🙂
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.Buffer(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
#"Removed Other Columns2"
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
11 Replies
How big is your table? Is there a specific reason why you have TransformColumnTypes nested inside the pivot instead of a separate step before the pivot?
Hi Jenn,
Thanks for your reply. Its a pretty big couple of files (one is 256k rows and the other is 60k). I rank all of the locations thatr contain the number of units per style (its for shoes) and then filter to just the top 3 locations and then pivot to get the Location 1, Location 2, and Location 3 top ranked. It seems that the issue happens as soon as I filter the ranked columns. That's why the TransforColumnTypes is at that stpe. No?
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.
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
