Nested table transformation takes too long

[montana310]

Hi All,

I perform frequently a task where I need to provide details regarding the list of order IDs. I have access to several files which contains order details that are merged to a separate table (Transactions) - please find the data sample below:

Transaction ID	Client ID	Date	Amount
24311	10315	14.01.2022	4417,22
24312	10316	14.01.2022	25565,70
24313	10317	14.01.2022	37430,62
24314	10318	14.01.2022	12763,08
24315	10319	14.01.2022	55400,19

The list of orders (Transactions_to_verify) inlcudes a one column:

Transaction ID

24311

24312

24313

24314

24315

This list needs be expanded by Client ID, Date and Amount columns from the Transactions table and in the next step the details of the maximum order amounts in previous periods need to added either - please see the output table layout below:

Transaction ID	Client ID	Date	Amount	Previous max order date	Previous max order amount
24311	10315,00	14.01.2022	4417,22	05.01.2022	59301,03
24312	10316,00	14.01.2022	25565,70	05.01.2022	99617,19
24313	10317,00	14.01.2022	37430,62	12.01.2022	50802,83
24314	10318,00	14.01.2022	12763,08	05.01.2022	90098,31
24315	10319,00	14.01.2022	55400,19	01.01.2022	82585,85

My power query code is:

let
Source = Excel.CurrentWorkbook(){[Name="Order_check"]}[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

The Max_order_amount step takses 10-20 min, depend on nember of orders to verify and other modifications of this step which use e.g. List.Max brought the same efect. This is a bottleneck and I could not find any solutions on how to accelarate the performance of the code.

I also attached a link  to the file with the dummy data.

Might you be able to advise how to modify the code and improve the performance?

[ams1 · Accepted solution]

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.

[montana310]

Hi ams1,

Many thanks for you response. I have implemented the sligthly modified first version of the code. To summarize:

1. tables have been loaded to the code and Tuble.Buffer() has been used before Table.NestedJoin()

2. I have reduced a number of columns in my orginal dataset to mininum and "assigned" Transactions_to_verify to Transactions

3. I am not a big fun of sorting and using the Table.FirstN() therefore I have left Table.Max()

These 3 point signifcatly accelerate the code, one more time many thanks for your advice. 

It would be appreciated if you also share other tips than can be used in the code 🙂