[SOLVED] Comparing matching columns between two tables using Power Query (when number of columns is variable)

Comparing matching columns between two tables using Power Query (when number of columns is variable)

I am building a query to compare two tables. The tables will always have the same columns/column names. The tables are merged and expanded such that all column names are suffixed with "old" or "new" (in order to rearrange them alphabetically) depending on which table they came from.

Then this script:

CompareColumns = Table.AddColumn(priorStep, "newColumnName", each
        [Column1.old] <> [Column1.new] or 
        [Column2.old] <> [Column2.new] or 
        [Column3.old] <> [Column3.new] or
        ...
        [ColumnN.old] <> [ColumnN.new])

I could create a text string by concatenating all column names with <>/or. But can Power Query convert this string into a function? Is there any method to create a function which compares all columns of same name but with different suffix?

Edit: I discovered Expression.Evaluate() but realized its a lot harder to create the text string than I thought. Open to any suggestions.

Solution

Assuming there is always an .old for a .new column in the same table, then this would count the number of differences on each row from the old and new fields. A zero would mean they all match

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
x = List.RemoveNulls(List.Transform(Table.ColumnNames(Source), each if Text.Contains(_,"old") then _ else null)),
y = List.RemoveNulls(List.Transform(Table.ColumnNames(Source), each if Text.Contains(_,"new") then _ else null)),
#"Added Custom" = Table.AddColumn(Source, "CountDifferences", each List.Count(List.Difference(Record.ToList(Record.SelectFields(_,x)),Record.ToList(Record.SelectFields(_,y)))))
in #"Added Custom"