Performance issue with Power query step

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

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"