Find out Cleared Transactions Reconciliation based multiple dates

Hi amol0512,

for future requests:

• be more pricise with description please
• provide sample data as table so we can copy/paste
• provide expedted result based on sample data

Try this (you have to put your table to same state as my Source step so it should looks like this:

And then replase whole code of my Source step with your table reference.

Result

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevtValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,

    Source = #table(type table[ReportDate=date, Custom=table],
        { {#date(2024,3,1), #table(type table[id=Int16.Type, trans_id=Int64.Type], {{1, 123}, {2, 456}, {3, 789}})},
          {#date(2024,3,4), #table(type table[id=Int16.Type, trans_id=Int64.Type], {{1, 123}, {2, 654}, {3, 987}})},
          {#date(2024,3,5), #table(type table[id=Int16.Type, trans_id=Int64.Type], {{1, 234}, {2, 567}, {3, 897}})},
          {#date(2024,3,6), #table(type table[id=Int16.Type, trans_id=Int64.Type], {{1, 567}, {2, 499}, {3, 799}})} }),
    Ad_ShiftedCustom = fnShift(Source, "Custom", -1, "Shifted"),
    #"Expanded Custom" = Table.ExpandTableColumn(Ad_ShiftedCustom, "Custom", {"id", "trans_id"}, {"id", "trans_id"}),
    Ad_Check = Table.AddColumn(#"Expanded Custom", "Check", each try if List.Contains([Shifted][trans_id], [trans_id]) then true else false otherwise null, type nullable logical)
in
    Ad_Check