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

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    22 million operations? What could possibly go wrong?  🙂

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

    It's almost as if I'm using Power BI for something it's not intended for becasue IT haven't given me the tools I've been asking for for the last 3 years

  • lbendlin's avatar
    lbendlin
    Super User
    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.

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

    Many thanks for your contribution.  I will take a look through what you have given me.