Numbers reconciliation in PowerQuery with M language

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 ?

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.

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

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"