Performance issue with Power query step

[sendilc]

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

[BA_Pete · Accepted 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

[sendilc]

Hi I tried this option as well and it also improved my performance time. 

[AlexisOlson · Accepted solution]

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"

[sendilc]

Hi, Thanks for the solution, this change got my query to execute in seconds. Thanks again. 

[BA_Pete · Accepted 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