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
- jennratten
Super User
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?
- jennratten
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.
- wdx223_Daniel
Community Champion
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
- wdx223_Daniel
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
