Comparing two tables with List Function

[Ekaterina_]

Hello everyone, 

I met some issues with matching two tables. In my table1 I have products listed during a time period with their corresponding status and category. In my table2 I have ProcessStepValues acording the category and status, i.e. if a product from category A moves from one day to the other from status  Open to On Hold it should receive the value 40. Here are the tables:

Table1:

Date	Product	Category	Status
02.03.2024	Shoes	A	Open
03.03.2024	Shoes	A	Open
04.03.2024	Shoes	A	On Hold
05.03.2024	Shoes	A	Open
03.03.2024	Book	B	Open
04.03.2024	Book	B	Closed
05.03.2024	Book	B	Closed
04.03.2024	Earings	C	Open
05.03.2024	Earings	C	On Hold
06.03.2024	Earings	C	Closed

Table2:

Category	Status	ProcessStepValues
A	Open	40
A	On Hold	60
A	Closed	0
B	Open	30
B	On Hold	70
B	Closed	0
C	Open	55
C	On Hold	45
C	Closed	0

• Now I want to create a custom column in table1 where the products get a ProcessStepValue from table 2, this means if Product X from category A moves from status Open to On Hold it receives the values 40, if there wasn't any move from one day to the other, the value will be 0, if it moves from On Hold back to Open the value should be -40 and if it moves from Open to Closed the value should be the sum of the values from Open to On Hold + On Hold to Closed = 100, so it should look like this:

Date	Product	Category	Status	ProcessStepValues
02.03.2024	Shoes	A	Open	0
03.03.2024	Shoes	A	Open	0
04.03.2024	Shoes	A	On Hold	40
05.03.2024	Shoes	A	Open	-40
03.03.2024	Book	B	Open	0
04.03.2024	Book	B	Closed	100
05.03.2024	Book	B	Closed	0
04.03.2024	Earings	C	Open	0
05.03.2024	Earings	C	On Hold	55
06.03.2024	Earings	C	Closed	45

Everytime I try sth out I get a cycle problem, could someone help me please?

Thank you in advance!

[dufoq3 · Accepted solution]

Hi @Ekaterina_, there are many ifs in this query so I'm not sure about speed if you have bigger dataset, byt give it a try 🙂

Result

let
    fnProcessStepValues = 
        (tbl as table) as table =>
        let
            SortByDate = Table.Sort(tbl, {{"Date", Order.Ascending}}),
            BufferSelectedColumns = Table.Buffer(Table.SelectColumns(SortByDate,{"Category", "Status", "ProcessStepValues", "Index"})),
            Lg_ProcessStepValues = List.Generate(
                ()=> [ x = 0, y = 0 ],
                each [x] < Table.RowCount(BufferSelectedColumns),
                each [ x = [x]+1, 
                    y = if BufferSelectedColumns{x}[Status] = BufferSelectedColumns{[x]}[Status] //Same status
                            then 0 else
                        if BufferSelectedColumns{x}[Status] = "Closed" and BufferSelectedColumns{[x]}[Status] = "Open" //Actual row [Status] = "Closed", Prev row [Status] = "Open"
                            then List.Sum(Table.SelectRows(ChangedTypeTrim_Table2, (a)=> a[Category] = BufferSelectedColumns{x}[Category])[ProcessStepValues]) else
                        if BufferSelectedColumns{x}[Status] = "Open" and BufferSelectedColumns{[x]}[Status] = "Closed" //Actual row [Status] = "Open", Prev row [Status] = "Closed"
                            then -List.Sum(Table.SelectRows(ChangedTypeTrim_Table2, (a)=> a[Category] = BufferSelectedColumns{x}[Category])[ProcessStepValues]) else
                        if BufferSelectedColumns{x}[Index] > BufferSelectedColumns{[x]}[Index] //Actual row [Index] is greater than Prev row [Index]
                            then BufferSelectedColumns{[x]}[ProcessStepValues] else -BufferSelectedColumns{x}[ProcessStepValues] ],
                each [y]
            ),
            RemovedColumns = Table.RemoveColumns(tbl, {"ProcessStepValues", "Index"}),
            Merged = Table.FromColumns(Table.ToColumns(RemovedColumns) & {Lg_ProcessStepValues}, Value.Type(RemovedColumns & #table(type table[ProcessStepValues=number],{})))
        in 
            Merged,
            
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSMzDWMzIwMlFQ0lEKzshPLQbSjkDsX5Cap6AUqwNUY0yEGhOcavIUPPJzUiCqTEm1zSk/PxtMQ9VgsQykBKrCOSe/OBWbVTjUoJjjmliUmZcOcpAzqoNM8ahC9pwZTB1CmQJUHczSWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t, Category = _t, Status = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIvSM1TANImBkqxOlChPAWP/JwUkKgZQtQ5J784FSwIEXNC0myMJATRDGSZIwTR9Toj6TU1RQghLDZBiEI0Q/TGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Status = _t, ProcessStepValues = _t]),
    ChangedTypeTrim_Table1 = Table.TransformColumns(Table1,{{"Date", each Date.From(_), type date}} & List.Transform({"Product", "Category", "Status"}, (colName)=> { colName, Text.Trim, type text })),
    // Buffered
    ChangedTypeTrim_Table2 = Table.Buffer(Table.TransformColumns(Table2,{{"ProcessStepValues", each Number.From(_), type number}} & List.Transform({"Category", "Status"}, (colName)=> { colName, Text.Trim, type text }))),
    Table2_AddedIndex = Table.AddIndexColumn(ChangedTypeTrim_Table2, "Index", 0, 1, Int64.Type),
    MergedQueries = Table.NestedJoin(ChangedTypeTrim_Table1, {"Category", "Status"}, Table2_AddedIndex, {"Category", "Status"}, "Table2", JoinKind.LeftOuter),
    ExpandedTable2 = Table.ExpandTableColumn(MergedQueries, "Table2", {"ProcessStepValues", "Index"}, {"ProcessStepValues", "Index"}),
    GroupedRows = Table.Group(ExpandedTable2, {"Product", "Category"}, {{"All", each fnProcessStepValues(_), type table}}),
    CominedAll = Table.Combine(GroupedRows[All])
in
    CominedAll

[Ekaterina_]

Wow, amazing. Thank you very much!

[dufoq3]

You're welcome Ekaterina.