Numbers reconciliation in PowerQuery with M language

[olena2212]

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 only have saldo for each vendor

for example I have vendor Apple
i need to link ids to each other, but the thing is that one id can be used as linked id only for the first found row within specific vendor
this is an initial dataset:

first row should be linked to the second one
second row should be linked to the first one
third row should not be linked to any row since the list does not have corresponding entries which have not been paired yet

this is my query:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Vendor name", type text}, {"Amount", Int64.Type}, {"Date", type text}}),
#"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})
in
Custom1

here is achieved result with this query:
achieved result with my query

the problem is that row 3 should not be paired to row 1 since row 1 and row 2 already form a pair

so linked id for row 3 should be blank

could you please help me out? thank you very much in advance!

Best regards

[huytuan]

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdJGpkqxOtFKRkhCulAxYyxiJkC2b2ZyUX5xfloJkG1sABY2RRPWhYqboYkbQYTN0ZVDxS2QbDSBWGiJ6VZDAyQxUzOIGLKfDA0gxhmi+Aqm0hiLm2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Vendor name" = _t, Amount = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Amount", type number}, {"Id", Int64.Type}}), #"Added Custom" = Table.AddColumn(ChangedType, "N", each Number.Abs([Amount])), #"Grouped Rows" = Table.Combine(Table.Group(#"Added Custom", {"Vendor name", "N"}, {"T", each let tocol= Table.ToColumns(_) in Table.FromColumns(List.RemoveLastN(tocol)&{List.Combine( List.Transform(List.Split(tocol{0},2),(i)=>if List.Count(i)=1 then {null} else List.Reverse(i)))},Table.ColumnNames(Source)&{"Link"}) })[T]), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Id", Order.Ascending}}) in #"Sorted Rows"

[slorin]

Hi, @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
Result

Stéphane

[AlienSx · Accepted solution]

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
    z

[olena2212]

thank you very much! it works!
can you please explain how this recursive function works?

[AlienSx]

@olena2212 "s" and "c" are lists of rows of our table. We start with full list (step "rows") and empty "c", take first row ("a" step), find a row with the same vendor but opposite amount ("match" step). If found - remove "a" and "match" from "s" and add them into "c" with additional item -  "Index ID". When "s" becomes empty, function returns "c". Until then we run function again with updated "s" and "c". 

[olena2212]

cool!

[dufoq3]

Hi @olena2212, check this.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdJGpkqxOtFKRkhCulAxYyxiJkC2b2ZyUX5xfloJkG1sABY2RRPWhYqboYkbQYTN0ZVDxS2QbDSBWGiJ6VZDAyQxUzOIGLKfDA0gxhmi+Aqm0hiLm2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Vendor name" = _t, Amount = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Amount", type number}, {"Id", Int64.Type}}),
    fn_LinkedId = (tbl as table)=>
    [ a = Table.Buffer(tbl[[Id], [Amount]]),
      lg = List.Generate(
              ()=> [ x = 0,
                     id = Table.SelectRows(a, (w)=> w[Amount] = a{x}[Amount] * -1)[Id]{0}?,
                     y = #table({"col1", "col2"}, if id <> null then {{ a{x}[Id], id }} else {}),
                     z = Table.SelectRows(a, (w)=> not List.Contains(y[col1] & y[col2], w[Id])) ],
              each [x] < Table.RowCount(a),
              each [ x = [x]+1,
                     id = [y]{[col2 = a{x}[Id]]}?[col1]? ?? Table.SelectRows([z], (w)=> w[Amount] = a{x}[Amount] * -1)[Id]{0}? ,
                     y = [y] & #table({"col1", "col2"}, if id <> null then {{ a{x}[Id], id }} else {}),
                     z = Table.SelectRows([z], (w)=> not List.Contains(y[col1] & y[col2], w[Id])) ],
              each [id]
        ),
      b = Table.FromColumns(Table.ToColumns(tbl) & {lg}, Value.Type(ChangedType & #table(type table[Linked Id=Int64.Type],{})))
    ][b],
    GroupedRows = Table.Group(ChangedType, {"Vendor name"}, {{"Fn", each fn_LinkedId(_), type table}}),
    CombinedFn = Table.Combine(GroupedRows[Fn])
in
    CombinedFn

[olena2212]

that would be a correct result
correct result

[dufoq3]

Hi, I've edited my previous code. It works as expected now.

[olena2212]

Hi, 

thank you!

I tried to expand my table with some more rows for test:

expanded table

after this unfortunately one pair is not matched

marked rows were not matched to each other

it seems like it only forms a pair if negative number appears after positive one in the list? can that be?

thanks in advance!

[v-junyant-msft]

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.

[olena2212]

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

[olena2212]

thank you! I will test it🙂

[lbendlin]

third row should not be linked to any row since the list does not have corresponding entries which have not been paired yet

why should row 3 not be paired with row 8 ?

[olena2212]

Because when we sum row 3 and row 8 it will not result to zero

if row 8 would have 25 then they should have been linked with each other

i search for the same amount but with negative sign and check whether the rows have same vendor then they should form a pair and be linked to each other