Here's one way you could determine reconciled status for the two cases you described above.
- Select Table1's Amount1 column. Click the Add Column ribbon tab, then Standard -> Multiply. Enter -1 to get the negated version of each number.
- Do a self-merge of Table1 against itself, matching on Amount1 and the negated Amount1.
- Any rows that have a single row in the merge column are reconciled.
You can do a similar self-merge for the different table case, without negating the numbers first.
EDIT: Here's some M code that demonstrates a possible way to do this for the same-table case.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Check Number", Int64.Type}, {"Amount 1", Int64.Type}, {"Amount 2", Int64.Type}}),
#"Inserted Multiplication" = Table.AddColumn(#"Changed Type", "Multiplication", each if [Amount 1] = 0 then null else [Amount 1] * -1, type number),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Neg Amount 1"}}),
#"Inserted Multiplication1" = Table.AddColumn(#"Renamed Columns", "Multiplication", each if [Amount 2] = 0 then null else [Amount 2] * -1, type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Multiplication1",{{"Multiplication", "Neg Amount 2"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns1", {"Amount 1"}, #"Renamed Columns1", {"Neg Amount 1"}, "Renamed Columns1", JoinKind.LeftOuter),
#"Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns1", {"Check Number"}, {"Renamed Columns1.Check Number"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Renamed Columns1", {"Amount 2"}, #"Expanded Renamed Columns1", {"Neg Amount 2"}, "Expanded Renamed Columns1", JoinKind.LeftOuter),
#"Expanded Expanded Renamed Columns1" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Renamed Columns1", {"Check Number"}, {"Expanded Renamed Columns1.Check Number"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Expanded Renamed Columns1",{"Renamed Columns1.Check Number"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Renamed Columns1.Check Number] <> null),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows"),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Duplicates",{{"Renamed Columns1.Check Number", "Reconciled Check Numbers"}})
in
#"Renamed Columns2"