Forum Discussion
Solved
Numbers reconciliation in PowerQuery with M language
Hello everyone, i need to reconcile numbers in powerquery within specific vendor name (to link the rows to each other and then i will filter out the paired rows which result to zero), so that i o...
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], rows = List.Buffer(Table.ToRows(Source)), f = (s, c) => [a = s{0}, match = List.Select(s, (x) => {a{1}, a{2}} = {x{1}, -x{2}}){0}? ?? {}, upd_c = if List.IsEmpty(match) then c & {a} else c & {a & {match{0}}} & {match & {a{0}}}, upd_s = List.RemoveMatchingItems(s, {a, match}), next = if List.IsEmpty(s) then c else @f(upd_s, upd_c)][next], upd_rows = f(rows, {}), z = Table.FromList(upd_rows, (x) => x, Table.ColumnNames(Source) & {"Linked ID"}, null) in zHi, olena2212 AlienSx , dufoq3
Another solution with Table.Group
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Abs = Table.AddColumn(Source, "Abs", each Number.Abs([Amount])),
Group = Table.Group(Abs, {"Vendor name", "Abs"},
{{"Data", (x) => let
positive = Table.ToColumns(Table.SelectRows(x, each [Amount]=[Abs])),
negative = Table.ToColumns(Table.SelectRows(x, each [Amount]=-[Abs])),
data = Table.FromColumns(positive & {negative{0}}, Table.ColumnNames(Abs) & {"Linked ID"}) &
Table.FromColumns(negative & {positive{0}}, Table.ColumnNames(Abs) & {"Linked ID"})
in data}}),
Result =
Table.Sort(
Table.SelectRows(
Table.RemoveColumns(
Table.Combine(Group[Data]),
{"Abs"}),
each [Id]<>null),
{{"Id", Order.Ascending}})
in
ResultStéphane
Hi olena2212 ,
My idea would be to add a step to what you have in your existing implementation, i.e. for each Linked Id keep only the record with the smallest row of Index and change the other records to null.
Here is the whole M function in the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdJGpiDCwMhEz1DPUClWJ1rJCElWF1naGCxtjF3aSM8ELG0CFPDNTC7KL85PKwGyjQ1gKoBGGICVmKIp0UVRA3GEGZoaI7gSYz1TsApzdFOQlZiDlVggOdXEFMkOoAmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Vendor name" = _t, Amount = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Vendor name", type text}, {"Amount", Int64.Type}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0,1,Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Linked Id", each
let
// Get the current row's vendor name, amount, and index
CurrentVendor = [Vendor name],
CurrentAmount = [Amount],
CurrentIndex = [Index],
// Filter for rows with the opposite amount and the same vendor that occur before and after the current row
FilteredRowsBefore = Table.SelectRows(#"Added Index", each [Vendor name] = CurrentVendor and [Amount] = -CurrentAmount and [Index] < CurrentIndex),
FilteredRowsAfter = Table.SelectRows(#"Added Index", each [Vendor name] = CurrentVendor and [Amount] = -CurrentAmount and [Index] > CurrentIndex),
// Find the first unpaired linked transaction before and after the current row, if any
FirstUnpairedLinkedRowBefore = if not Table.IsEmpty(FilteredRowsBefore) then Table.Last(FilteredRowsBefore) else null,
FirstUnpairedLinkedRowAfter = if not Table.IsEmpty(FilteredRowsAfter) then Table.First(FilteredRowsAfter) else null,
// Get the ID of the first unpaired linked transaction found in each direction, if any
LinkedIdBefore = if FirstUnpairedLinkedRowBefore <> null then Text.From(FirstUnpairedLinkedRowBefore[Id]) else null,
LinkedIdAfter = if FirstUnpairedLinkedRowAfter <> null then Text.From(FirstUnpairedLinkedRowAfter[Id]) else null,
// Combine the linked IDs from both directions
CombinedLinkedId = List.Combine({{LinkedIdBefore}, {LinkedIdAfter}})
in
CombinedLinkedId),
Custom1 = Table.TransformColumns(#"Added Custom", {"Linked Id", each Text.Combine(List.Distinct(List.RemoveNulls(_)), ", "), type text}),
#"Grouped Rows" = Table.Group(Custom1, {"Linked Id"}, {{"Min_Index", each List.Min([Index]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Linked Id", "Min_Index"}, Table, {"Linked Id", "Index"}, "Table", JoinKind.FullOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Id", "Vendor name", "Amount", "Date"}, {"Id", "Vendor name", "Amount", "Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Min_Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Id", "Vendor name", "Amount", "Date", "Linked Id"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Id", Order.Ascending}})
in
#"Sorted Rows"Your code I did not modify at all, I just added these steps at the end:
#"Grouped Rows" = Table.Group(Custom1, {"Linked Id"}, {{"Min_Index", each List.Min([Index]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Linked Id", "Min_Index"}, Table, {"Linked Id", "Index"}, "Table", JoinKind.FullOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"Id", "Vendor name", "Amount", "Date"}, {"Id", "Vendor name", "Amount", "Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Min_Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Id", "Vendor name", "Amount", "Date", "Linked Id"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Id", Order.Ascending}})
in
#"Sorted Rows"And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for your answer!
i added some more rows for test
marked rows are linked to 2 other rows
it does not seem to work properly
correct result would look like this:
correct result