Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jemsongs
Regular Visitor

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"

1 ACCEPTED SOLUTION

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

View solution in original post

11 REPLIES 11
wdx223_Daniel
Super User
Super User

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.

jennratten
Super User
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? 

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".

Hmmm...seems to still be crawling. Here's what I changed:

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.SelectRows(#"Reordered Columns", each [Rank] <= 3),
#"Removed Duplicates1" = Table.Distinct(#"Reordered Columns", {"Style-Color","Location-Qty"}),
#"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 pretty convinced it is the FilteredRows step that's killing it.

Please add Rank to the Removed Duplicates step and rerun. You can also try moving the Filtered Rows step up to just after Expanded Custom and moving Pivoted Column step to a new query and see what happens in terms of performance.  There is definitely room for optimization with this step:

 

#"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)),

Is there a particular reason why you are trying to do this with Power Query instead of DAX?  Often times DAX will do things like this more efficiently.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors