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 ...
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.
dufoq3
2 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