Forum Discussion
Dynamic comparison of 2 tables
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.
When you use an inner join you are turning a blind eye on all additions and deletions. Is that intended?
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Hi lbendlin
Inner join was specifically chosen because Left and Right Anti's are used for inserts and deletes in other queries (see the example).
I have included the example file here...
https://drive.google.com/file/d/1-zJLgcyuyUB5Ct1l50yOt7kGXTfZDWSN/view?usp=sharing
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.
I see it's cuteness 🙂 but it's knowing what has changed that's the crucial bit
it was worth a try. I'll work on the full formed approach.