Nested table transformation takes too long

Hi,

I really liked that you provided samples.

Below IS ugly, but it runs on my machine in 2-3 seconds 😊:

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name = "Transaction_table"]}[Content]),
    Transaction_table = Table.Sort(Source,{{"Client ID", Order.Ascending}, {"Date", Order.Ascending}}),
    Order_check = Table.Buffer(Excel.CurrentWorkbook(){[Name = "Order_check"]}[Content]),
    #"Merged Queries" = Table.NestedJoin(Transaction_table, {"Transaction ID"}, Order_check, {"Transaction ID"}, "Order_check", JoinKind.LeftOuter),
    #"Expanded Order_check" = Table.ExpandTableColumn(#"Merged Queries", "Order_check", {"Transaction ID"}, {"Transaction ID.1"}),
    #"Grouped Rows" = Table.Group(#"Expanded Order_check", {"Client ID"}, {{"All", each _, type table [Transaction ID=nullable number, Client ID=nullable number, Date=nullable date, Amount=nullable number, Transaction ID.1=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows(Table.FillUp([All],{"Transaction ID.1"}), each [Transaction ID.1] <> null)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom.2", each Table.SelectRows([All], each [Transaction ID.1] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Custom", "Custom.2"}),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.2", {"Transaction ID", "Client ID", "Date", "Amount"}, {"Transaction ID", "Client ID", "Date", "Amount"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom.2", "Custom.1", each Table.FirstN(Table.Sort(Table.SelectRows([Custom],(in_tbl)=> in_tbl[Date] < _[Date] ),{{"Amount", Order.Descending}}),1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Date", "Amount"}, {"Date.1", "Amount.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Date.1", "Previous max order date"}, {"Amount.1", "Previous max order amount"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Previous max order date", type date}})
in
    #"Changed Type"

IF above doesn't return the same thing as your query, then below Table.Buffer should improve your original query - it took like 15 seconds on my machine with your sample data.

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name = "Order_check"]}[Content]),
    //       ^^^^^^^^^^^^
    Transaction_tbl = Table.Buffer(Excel.CurrentWorkbook(){[Name = "Transaction_table"]}[Content]),
    //                ^^^^^^^^^^^^
    Merged_tbl = Table.NestedJoin(
        Source, {"Transaction ID"}, Transaction_tbl, {"Transaction ID"}, "Transaction_tbl", JoinKind.LeftOuter
    ),
    Order_details_expanded = Table.ExpandTableColumn(
        Merged_tbl, "Transaction_tbl", {"Client ID", "Date", "Amount"}, {"Client ID", "Date", "Amount"}
    ),
    Client_orders = Table.NestedJoin(
        Order_details_expanded, {"Client ID"}, Transaction_tbl, {"Client ID"}, "Transaction_tbl", JoinKind.LeftOuter
    ),
    Previous_orders = Table.AddColumn(
        Client_orders, "Custom2", each Table.SelectRows([Transaction_tbl], (in_tbl) => in_tbl[Date] < _[Date])
    ),
    Max_order_amount = Table.TransformColumns(Previous_orders, {"Custom2", each Table.Max(_, "Amount")}),
    Expand_max_order_amount_details = Table.ExpandRecordColumn(
        Max_order_amount, "Custom2", {"Date", "Amount"}, {"Previous max order date", " Previous max order amount"}
    )
in
    Expand_max_order_amount_details

There's more that could be done, but hope this is enough for you.

Please mark this as answer if it helped.