Comparing two tables columns by columns

[divumj]

Hi all

I have system where data flow from one system to another system and I have output from those two system, data is ditto same line by line.

Now I have to perform check to ensure Data flow is correct, for which I  have create a unique in both tables and then join them to create a new table, then compare two columns by creating a custom formula. But it is very time consuming as I have to compare 70 odds columns.

Is there any quick way to compare data columns to columns

Table 1

Unique  col A    colB

Xxxxx.     False.    False

Yyyyyy.    False.    True

Table 2

Unique.    Col A.  Col B

Xxxxx.       False  False

Yyyyy.       True.   True

[Anonymous · Accepted solution]

Hi @divumj ,
You can ttry the following code

let
    // Load Table 1
    Source1 = #"Table 1",
    // Load Table 2 and rename columns to avoid conflict
    Source2 = Table.RenameColumns(#"Table 2", List.Transform(Table.ColumnNames(#"Table 2"), each {_, _ & "2"})),
    // Merge Tables
    MergedTables = Table.NestedJoin(Source1, "Unique", Source2, "Unique2", "Table2", JoinKind.Inner),
    // Expand the merged table
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "Table2", Table.ColumnNames(Source2)),
    // Get the list of columns to compare
    ColumnsToCompare = List.RemoveItems(Table.ColumnNames(Source1), {"Unique"}),
    // Function to compare columns
    CompareColumns = (table as table, columns as list) as table =>
        List.Accumulate(
            columns,
            table,
            (state, current) =>
                Table.AddColumn(
                    state,
                    "Compare_" & current,
                    each if Record.Field(_, current) = Record.Field(_, current & "2") then "Match" else "Mismatch"
                )
        ),
    // Apply the comparison function
    Result = CompareColumns(ExpandedTable, ColumnsToCompare),
    // Remove unnecessary columns
    FinalResult = Table.SelectColumns(Result, List.Combine({{"Unique"}, List.Transform(ColumnsToCompare, each "Compare_" & _)}))
    in
        FinalResult

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[slorin]

Hi, @divumj 

let
Source = Table.Combine({Table1, Table2}),

Compare = (t) => 
[a = Table.DemoteHeaders(t),
b = Table.Transpose(a),
c = Table.CombineColumns(b,{"Column2", "Column3"}, each _{0} = _{1}, "Compare"),
d = Table.Transpose(c),
e = Table.PromoteHeaders(d)][e],

Columns = List.Difference(Table.ColumnNames(Source), {"Unique"}),
Group = Table.Group(Source, {"Unique"}, {{"Data", each Compare(Table.RemoveColumns(_, "Unique")), type table}}),
Expand = Table.ExpandTableColumn(Group, "Data", Columns, Columns)
in
Expand

Stéphane 

[divumj]

@slorin sorry I am not that good with query writing, could you please explain a bit of your code.

[slorin]

Hi  @divumj

another solution

let
Source = Table.Combine({Table1, Table2}),

Columns = List.Difference(Table.ColumnNames(Source), {"Unique"}),
Group = Table.Group(Source, {"Unique"}, 
    {{"Data", each #table(
        Columns, 
        {List.Skip(
           List.Transform(
              List.Zip({Record.ToList(_{0}), Record.ToList(_{1})}), 
              each _{0} = _{1}))}), type table}}),
Expand = Table.ExpandTableColumn(Group, "Data", Columns, Columns)
in
Expand

the principle is to combine 2 tables and then group according to the "Unique" column

We obtain 2 rows per grouping and we compare the values of these 2 rows (the first of table 1 and the second of table 2)

Stéphane

[PwerQueryKees]

It depends a bit on what you would see in case of differences.

Some approaches coming to mind:

• Do a merge on all columns full outer join. Expand the table and check for null in any field that should never have null and you have a discrepancy. Hard to diagnose where it is though.
• Or add an index column to both tables, unpivot all columns except the index column, Do an inner join on the index and attribute columns. Anywhere the 2 values are not equal you have a discrpancy. Will give a mess when you are missing a record somewhere.
• Or, same as last, but use an existing unique key instead of the index. Missing records are easier found.

I hope this gives you some ideas...

[divumj]

@PwerQueryKees Thanks I am almost doing same thing...but problem is our main focus is to not just identify records but also to look for any unwanted data transformation in any of the column...for which I have created a calculated filed in merge table...Colum a = table2.column a.

To write this formula I have literary type columns name one by one, which is very time consuming and I have to create multiple transformation on same grounds.