Add a column to an Embedded Table, as a transformation. Lookup Previous Row.

[joeneedstoknow2]

I have a Customer Fact Table in power query, showing each customer who placed an order.

There is a column in the customer fact table, called dbo _ Orders. dbo _ Orders is an embedded table, showing the order information for each customer. The Orders table has an Index column, and an ReadyDate column.

I need to write a function that Adds a column into the embedded table, for each Customer in the fact table. The goal of the function is to add a column that looks up the value of Date in the previous row. It must calculate in the embedded Orders table.

Can somone please help me write the function, and explain how to it works?

[joeneedstoknow2]

Amazing. Thank you

[jgeddes · Accepted solution]

Here is some code that should do what you need.

let
    nested_5493 = 
    #table(
        type table [OrderId=nullable number, OrderDate=nullable date, CustomerID=nullable number, Status=nullable text, Index=nullable number],
        {
            {496977, #date(2025,3,3), 5493, "Shipped", 0},
            {498804, #date(2025,3,10), 5493, "Shipped", 1},
            {514401, #date(2025,5,4), 5493, "Shipped", 2}
        }
    ),
    nested_5626 = 
    #table(
        type table [OrderId=nullable number, OrderDate=nullable date, CustomerID=nullable number, Status=nullable text, Index=nullable number],
        {
            {496988, #date(2025,2,3), 5626, "Shipped", 0},
            {498805, #date(2025,3,12), 5626, "Pending", 1},
            {514402, #date(2025,5,6), 5626, "Shipped", 2}
        }
    ),
    dim_table = 
    #table(
        type table [CustomerID=nullable number, Email=nullable text, dbo_Orders=table],
        {
            {5493, "cust5493@domain.com", nested_5493},
            {5626, "cust5626@domain.com", nested_5626}
        }
    ),
    add_nested_column = 
    Table.TransformColumns(
        dim_table, 
        {
            {"dbo_Orders", each Table.AddColumn(_, "PreviousOrder", (r)=> try [OrderDate]{r[Index] - 1} otherwise null, type date), type table}
        }
    )    
in
    add_nested_column

In the sample table I created...

I added a column to the nested tables that shows the previous OrderDate.