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
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.
Thanks. I made the changes but wasn't sure if I understood you correctly. Is this what you meant:
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"}),
#"Removed Duplicates1" = Table.Distinct(#"Reordered Columns", {"Location-Qty", "Rank"}),
#"Filtered Rows" = 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
#"Renamed Columns"I'm not sure if that worked as the refresh is about 5 minutes in and still going.
Thanks again for your help.
Almost, #"Removed Duplicates1" needs to be after #"Filtered Rows".