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 flo...
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

     

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