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

Question

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.

Yes, there is. Please supply a data sample as text and a screenshot of your expected output from that data sample. — Ron Rosenfeld
– Ron Rosenfeld, Commented Aug 14, 2024 at 18:49

horseyride · Accepted Answer · 2024-12-20 00:17:50Z

3

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"

edited Dec 20, 2024 at 0:17

answered Aug 14, 2024 at 14:35

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M.Sqrl Dec 18, 2024 at 21:38

Finally updated my PowerQuery with this. Replaced 40 if then else statements. Thanks.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related