Comparing one table by 2 columns or making 2 tables and comparing 2 columns...

Hi Anonymous - see if this helps.

I turns this table:

into this table. You'll note I added the 11-4444/12-4444 vendor because I needed something that didn't have the same amount to test my code.

See this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY3RCQAxCEN38buVVK1ws5Tuv8YpvUI/eiERAo84BjmqqzsVQuOwQBBFkBc0yyBD1VB2ZchGMoqF/KzspUT0ua7o8ai1aqHsxuEP6SciVyRjPZD5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SUPPLIER CODE" = _t, DATE = _t, DEBIT = _t, CREDIT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"DEBIT", Currency.Type}, {"CREDIT", Currency.Type}}),
    #"Added Net Amount" = Table.AddColumn(#"Changed Type", "Net Amount", each [DEBIT] - [CREDIT], type number),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Net Amount", "SUPPLIER CODE", "SUPPLIER CODE - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "SUPPLIER CODE - Copy", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Supplier Prefix", "Supplier Suffix"}),
    #"Grouped Rows" =
        Table.Group(#"Split Column by Delimiter", {"DATE"}, {{"All Rows", each _, type table [SUPPLIER CODE=nullable text, DATE=nullable date, DEBIT=nullable number, CREDIT=nullable number, Net Amount=number]}}),
    #"Added Balances True False" = Table.AddColumn(#"Grouped Rows", "Balances", each if List.Sum([All Rows][Net Amount]) = 0 then true else false, type logical),
    #"Added Same Suffix Different Prefix" = Table.AddColumn(#"Added Balances True False", "Same Suffix Different Prefix", each if [Balances] = false 
then false 
else
if List.Count(List.Distinct([All Rows][Supplier Prefix])) = 2 and List.Count(List.Distinct([All Rows][Supplier Suffix])) = 1 then true
else false, type logical),
    #"Filtered Rows" = Table.SelectRows(#"Added Same Suffix Different Prefix", each ([Same Suffix Different Prefix] = true)),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Filtered Rows", "All Rows", {"SUPPLIER CODE", "DEBIT", "CREDIT"}, {"SUPPLIER CODE", "DEBIT", "CREDIT"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"DATE", "SUPPLIER CODE", "DEBIT", "CREDIT"})
in
    #"Removed Other Columns"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Walk through each of the Applied Steps to see my logic. Post back with questions, or point out if I didn't do what you needed.