Forum Discussion
Merge and update History table from Update table having new or updated values
I'd recommend checking out these relevant resources for the self-referencing logic:
Self Referencing Tables in Power Query
Inserting text manually in a custom column and should be visible on refresh of the report
To do the update, I appended the tables together, grouped by ProductKey taking the latest version of the data, expanded the non-manual columns, and then merged back in the manual columns from the beginning of the query.
Please see the attached.
I would probably go with a full join. Then add new columns for each column in the History table: if a value in the column of the Update table is null take History, otherwise Update. Then remove old columns and rename the new ones to match the original History table columns.
Absolutely inelegant and inflexible, but reasonably straightforward and quick to implement and understand.
Kind regards,
JB