Forum Discussion
Dynamic comparison of 2 tables
- 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.
22 million operations? What could possibly go wrong? 🙂
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
- lbendlin2 years ago
Super User
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-Hornsby2 years agoFrequent Visitor
Many thanks for your contribution. I will take a look through what you have given me.