Forum Discussion

KF-Hornsby's avatar
KF-Hornsby
Frequent Visitor
2 years ago
Solved

Dynamic comparison of 2 tables

Hi folks,   I have 2 tables "Original" and "Updated" that match on a primary key.  I want to compare which (if any) records in the tables have changed and more specifically, what the change is.   ...
Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    2 years ago

    try this

     

     

     

     

    let
  OriginalB = Table.Buffer(Original),
  UpdatedB = Table.Buffer(Updated),
  Source = Table.NestedJoin(OriginalB, {"Key"}, UpdatedB, {"Key"}, "Updated", JoinKind.Inner), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Changes", 
    (k) =>
      List.Transform(
        List.Skip(Table.ColumnNames(OriginalB), 1), 
        each 
          let
            orig = Record.Field(Table.SelectRows(OriginalB, each [Key] = k[Key]){0}, _), 
            chg  = Record.Field(Table.SelectRows(UpdatedB, each [Key] = k[Key]){0}, _)
          in
            if orig = chg then null else _ & " : " & orig & " -> " & chg
      )
  ), 
  #"Expanded Changes" = Table.ExpandListColumn(#"Added Custom", "Changes"), 
  #"Filtered Rows" = Table.SelectRows(#"Expanded Changes", each ([Changes] <> null))
in
  #"Filtered Rows"

     

     

    and maybe also consider a Table.AddKey on the [Key] column.

    It will also be worth leafing through this series of articles : Chris Webb's BI Blog: Optimising The Performance Of Power Query Merges In Power BI, Part 1: Removing Columns (crossjoin.co.uk)

     

    There are better tools for this - for example Alteryx or RapidMiner or Knime.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

You want to compare Original against Updated across all columns except for Key?

 

There is a simple way of doing this that is rather cute

 

Table.Distinct(Original & Updated)

 

it does need some work to identify what is original and what is update but that can be done with another trick

 

Table.Distinct(Table.AddColumn(Original,"Source", each "Original") & Table.AddColumn(Updated,"Source",each "Updated"),Table.ColumnNames(Original))

 

It won't tell you which column changed but it will highlight the rows with changes.

 

KF-Hornsby's avatar
KF-Hornsby
Frequent Visitor
2 years ago

I see it's cuteness 🙂 but it's knowing what has changed that's the crucial bit

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    it was worth a try.  I'll work on the full formed approach.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      KF-Hornsby 

       

      This seems to work but I think it can be simplified.

       

       

      let
  Source = Table.NestedJoin(Original, {"Key"}, Updated, {"Key"}, "Updated", JoinKind.Inner), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Changes", 
    (k) =>
      List.Transform(
        List.Skip(Table.ColumnNames(Original), 1), 
        each 
          let
            orig = Record.Field(Table.SelectRows(Original, each [Key] = k[Key]){0}, _), 
            chg  = Record.Field(Table.SelectRows(Updated, each [Key] = k[Key]){0}, _)
          in
            if orig = chg then null else _ & " : " & orig & " -> " & chg
      )
  ), 
  #"Expanded Changes" = Table.ExpandListColumn(#"Added Custom", "Changes"), 
  #"Filtered Rows" = Table.SelectRows(#"Expanded Changes", each ([Changes] <> null))
in
  #"Filtered Rows"

       

       

       

      • KF-Hornsby's avatar
        KF-Hornsby
        Frequent Visitor
        2 years ago

        Awesome - I've added the query but at the moment I'm getting 

        "Evaluation resulted in a stack overflow and cannot continue"

        My dataset is 32 columns by 675,000 rows so I'm not sure if that's just too big or there's some other nuance I'm missing.  I'll investigate deeper.