Forum Discussion
amol0512
2 years agoHelper I
Find out Cleared Transactions Reconciliation based multiple dates
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 ...
dufoq3
2 years agoCommunity Champion
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
2 years agoHelper I
Hi, Data is very sensitive hence can't share with anyone but it's very similar to what you have prepared.
- dufoq32 years agoCommunity Champion
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