Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am using the following power query statement to identify the first order based on Order ID of a customer and flag the minimum Order ID with "Yes" and rest as "No". I have a index column which is a running sequence of rows in the table. This query is run over 10K records. The query works fine but the execution time is high.
The issues that I am having are:
It takes a lot of time to execute; ~ 25 mins and in some instances, it takes even longer. Is there a better way to handle this.
The transformations following the above step is also taking a similar time to execute. Hence the overall ETL is taking a longer duration. Pls let me know if anything is wrong with this query or is ther a better way to handle.
+++++++++++++++++++++
Table.ReplaceValue(#"Customer",
each [First Order],
each if [Index] = List.Min(Table.SelectRows(#"Customer",(x)=>x[Customer ID]=[Customer ID])[Index]) then "Yes" else "No",
Replacer.ReplaceValue,
{"First Order"})
++++++++++++++++++++++++++
Data Before Query
Cust ID Order ID Index First Order
1 100 1
1 1011 2
1 101234 3
2 110 4
3 1001 5
3 10043 6
Resulting Query
Cust ID Order ID Index First Order
1 100 1 Yes
1 1011 2 No
1 101234 3 No
2 110 4 Yes
3 1001 5 Yes
3 10043 6 No
Solved! Go to Solution.
Hi @sendilc ,
Assuming your [Order ID] is numerical and incremenatally increases with each new order, you can use a nested index to get an order sequence per customer:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YGxDQ2ROUbGJmCuEYhrCFFnDNFjiMwxMVaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cust ID" = _t, #"Order ID" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Cust ID", Int64.Type}, {"Order ID", Int64.Type}}),
groupCustID = Table.Group(chgTypes, {"Cust ID"}, {{"data", each _, type table [Cust ID=nullable number, Order ID=nullable number]}}),
sortNestedAsc = Table.TransformColumns(groupCustID, {"data", each Table.Sort(_, {{"Order ID", Order.Ascending}})}),
addNestedIndex = Table.TransformColumns(sortNestedAsc, {"data", each Table.AddIndexColumn(_, "orderSeq", 1, 1)}),
expandNestedColumn = Table.ExpandTableColumn(addNestedIndex, "data", {"Order ID", "orderSeq"}, {"Order ID", "orderSeq"})
in
expandNestedColumn
Example Output:
Here, then, any [orderSeq] that = 1 is the first order.
Pete
Proud to be a Datanaut!
Group by [Cust ID] taking the min over [Index], then merge that onto the original table. Expand the [First Index] column and define [First Order] to be Yes if [Index] = [First Index].
Result:
Full sample query you can paste into the Advanced Editor of a new Blank Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACRSrE6ML4hiDJCFjAyNgEyjMFCRiAhQ5AeEzDfGGIGSKUpsoAJiDZTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cust ID" = _t, #"Order ID" = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Cust ID", Int64.Type}, {"Order ID", Int64.Type}, {"Index", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Cust ID"}, {{"First Index", each List.Min([Index]), type nullable number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Cust ID"}, #"Grouped Rows", {"Cust ID"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"First Index"}, {"First Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "First Order", each if [Index] = [First Index] then "Yes" else "No", type text)
in
#"Added Custom"
Hi I tried this option as well and it also improved my performance time.
Group by [Cust ID] taking the min over [Index], then merge that onto the original table. Expand the [First Index] column and define [First Order] to be Yes if [Index] = [First Index].
Result:
Full sample query you can paste into the Advanced Editor of a new Blank Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MACRSrE6ML4hiDJCFjAyNgEyjMFCRiAhQ5AeEzDfGGIGSKUpsoAJiDZTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cust ID" = _t, #"Order ID" = _t, Index = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Cust ID", Int64.Type}, {"Order ID", Int64.Type}, {"Index", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Cust ID"}, {{"First Index", each List.Min([Index]), type nullable number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Cust ID"}, #"Grouped Rows", {"Cust ID"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"First Index"}, {"First Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "First Order", each if [Index] = [First Index] then "Yes" else "No", type text)
in
#"Added Custom"
Hi, Thanks for the solution, this change got my query to execute in seconds. Thanks again.
Hi @sendilc ,
Assuming your [Order ID] is numerical and incremenatally increases with each new order, you can use a nested index to get an order sequence per customer:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MFCK1YGxDQ2ROUbGJmCuEYhrCFFnDNFjiMwxMVaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cust ID" = _t, #"Order ID" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Cust ID", Int64.Type}, {"Order ID", Int64.Type}}),
groupCustID = Table.Group(chgTypes, {"Cust ID"}, {{"data", each _, type table [Cust ID=nullable number, Order ID=nullable number]}}),
sortNestedAsc = Table.TransformColumns(groupCustID, {"data", each Table.Sort(_, {{"Order ID", Order.Ascending}})}),
addNestedIndex = Table.TransformColumns(sortNestedAsc, {"data", each Table.AddIndexColumn(_, "orderSeq", 1, 1)}),
expandNestedColumn = Table.ExpandTableColumn(addNestedIndex, "data", {"Order ID", "orderSeq"}, {"Order ID", "orderSeq"})
in
expandNestedColumn
Example Output:
Here, then, any [orderSeq] that = 1 is the first order.
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.