Find out Cleared Transactions Reconciliation based multiple dates

[amol0512]

I am using multiple datewise excel files and trying to find out  No of Cleared transactions (Cleared Transactions=Transactions which are not appearing next day). I have current approach as per below power query but it's taking hours and hours to refresh the entire data. Need to know if anybody has better approach. 

1. Get excel files from folder (files are named with date)
2. Sorted files on this date column
3. Added Index column
4. Get next day table in front of each day with Index+1 and select only one column trans_id 
5. Expanded actual data and updated True False for matching transactions with each List.Contains([Custom.2],[trans_id])   

[dufoq3]

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

[amol0512]

@dufoq3,

It's working but it takes long to refresh. it's already 45 minutes but hardly 7-8 files are refreshed. (BTW I am not loading Shifted column that contain shifted tables)

[dufoq3]

Hi,

1. how big are your source excel files?
2. I'm don't understand what do you mean that you are not loading "Shifted column that contain shifted tables"

[amol0512]

Files are not too big on an avg 2000 kb each file. 

Shifted column.

[dufoq3]

Could you upload 3 or 4 of your tables i.e. to google drive and send here a link? Don't forget for permissions. If there are not sensitive data. If there are delete sensitive data please

[amol0512]

Hi, Data is very sensitive hence can't share with anyone but it's very similar to what you have prepared. 

[dufoq3]

Try this, but in my opinion both my queries are fast. Maybe there is a problem with some step before my transformation.

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_WithReportDate = Table.AddColumn(Source, "With ReportDate", each Table.AddColumn([Custom], "ReportDate", (x)=> [ReportDate], type date), type table),
    Ad_ShiftedCustom = fnShift(Ad_WithReportDate, "Custom", -1, "Shifted"),
    Ad_Merge = Table.AddColumn(Ad_ShiftedCustom, "Merge", each try Table.NestedJoin([Custom], {"trans_id"}, [Shifted], {"trans_id"}, "Result", JoinKind.LeftOuter) otherwise null, type table),
    Check = List.Transform(List.Combine(List.Transform(List.RemoveLastN(Ad_Merge[Merge],1), each [Result])), (x)=> if Table.RowCount(x) > 0 then true else false),
    Combined_SourceCheck = Table.FromColumns(Table.ToColumns(Table.Combine(Ad_WithReportDate[With ReportDate])) & {Check}, Value.Type(Ad_WithReportDate{0}[With ReportDate] & #table(type table[Check=logical], {{}})))
in
    Combined_SourceCheck