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.
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.
Many thanks for your contribution. I will take a look through what you have given me.