Forum Discussion

joeneedstoknow2's avatar
joeneedstoknow2
New Member
3 months ago
Solved

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

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?

Need Help
  • jgeddes's avatar
    jgeddes
    3 months ago

    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, "[email protected]", nested_5493},
            {5626, "[email protected]", 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.

     

2 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    3 months ago

    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, "[email protected]", nested_5493},
            {5626, "[email protected]", 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.