Comparing two tables with List Function

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