Forum Discussion

divumj's avatar
divumj
Helper I
1 year ago
Solved

Comparing two tables columns by columns

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

General Comment
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

     

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    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's avatar
      divumj
      Helper I
      1 year ago

      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.

  • slorin's avatar
    slorin
    Super User
    1 year ago

    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's avatar
      divumj
      Helper I
      1 year ago

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

      • slorin's avatar
        slorin
        Super User
        1 year ago

        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

         

         

  • Ahmedx's avatar
    Ahmedx
    Super User
    1 year ago

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