Forum Discussion
Merge and update History table from Update table having new or updated values
I have 2 tables viz., a History table that should get updated from an Update table whenever there are new or updated row values coming in the Update table.
History table:
Please note: the History table has extra Manual1 & Manual2 columns that may contain user updated values.
Update table:
The Update table may not have those columns, but only the standard columns from `Product Key` till `Out of Stock` column.
- ORANGE rows of History table are present in Update table, but have updated values.
- YELLOW row of History table has same values in Update table.
- GREEN rows of History table are not present in Update table.
- BLUE row of Update table is not present in History table, i.e. it is a New row coming in data.
What i need is,
- ORANGE row values from Update table, get updated into History table, but their Manual entry column values remain unchanged.
- YELLOW values remain unchanged. Also their Manual entry columns remain unchanged.
- GREEN values remain unchanged, as they are not present in Update table.
- History table gets new BLUE row values added to it from Update table, with blanks for Manual entry columns.
I have tried various solutions, but no joy for some of the reasons listed below:
- History table has more columns than Update table, so exact matching of row values (using concatenation) is not possible.
- Merging with Left.AntiJoin gives only the new or updated values from Update table and removes other rows.
- How to update rows with new or updated data without affecting the existing values in the Manual entry columns of History table.
Can anyone help urgently with this frustrating problem of updating a History table from another table with new or updated data, where their number of columns are different?
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.
10 Replies
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.
Hi AlexisOlson ,
This is so consice, but brilliant! 🙂
Can you help me with my below queries?
On opening Query editor, Updateable Table gives the following error on this line:
Formula.Firewall: Query 'UpdateableTable' (step 'AppendedQuery') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.AppendedQuery = Table.Combine({AddedVersion, NewData})- I see you have joined ProductKey column with Product column in step "Grouped Rows".
#"Grouped Rows" = Table.Group(#"Appended Query", {"ProductKey", "Product"}, {{"All Rows", each Table.Max(_, "Version"), type record}})In real data, the Product column may not be unique i.e. ProductKeys may have same Product names. How do we handle that? Is it possible to only use the ProductKey which is unique?
- Is there a way to make the column names selection dynamic, rather than hard-coded? There could be many standard columns in UpdateableTable.
#"Expanded All Rows" = Table.ExpandRecordColumn(#"Grouped Rows", "All Rows", {"Status", "Date", "Forecast", "Out of stock"}, {"Status", "Date", "Forecast", "Out of stock"})- Can Manual entry column names in last step be made dynamic?
Expanded Expanded All Rows = Table.ExpandTableColumn(#"Merged Queries", "Expanded All Rows", {"Manual1", "Manual2"}, {"Manual1", "Manual2"})Best,
Sifar
Read this for info on Formula.Firewall:
https://www.thepoweruser.com/2019/03/12/data-privacy-and-the-formula-firewall/
(The simplest solution is the 'Always ignore Privacy Level settings' under Query Options > Privacy.)
You can use whatever column or combination of columns you'd like to define what you consider unique. You can certainly drop the Product column. and only group over ProductKey.
Here's a bit more dynamic version of the query where I've added steps to generate lists of column names.
let Source = Excel.CurrentWorkbook(){[Name="UpdateableTable"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductKey", Int64.Type}, {"Product", type text}, {"Status", type text}, {"Date", type date}, {"Forecast", Int64.Type}, {"Out of stock", type text}, {"Manual1", type text}, {"Manual2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Version", each 0, Int64.Type), #"Appended Query" = Table.Combine({#"Added Custom", NewData}), #"Grouped Rows" = Table.Group(#"Appended Query", {"ProductKey"}, {{"All Rows", each Table.Max(_, "Version"), type record}}), ColumnNames = List.Select(Table.ColumnNames(NewData), each _ <> "ProductKey"), #"Expanded All Rows" = Table.ExpandRecordColumn(#"Grouped Rows", "All Rows", ColumnNames, ColumnNames), #"Merged Queries" = Table.NestedJoin(#"Expanded All Rows", {"ProductKey"}, #"Changed Type", {"ProductKey"}, "Expanded All Rows", JoinKind.LeftOuter), ManualColumns = List.Difference(Table.ColumnNames(#"Changed Type"), Table.ColumnNames(NewData)), #"Expanded Expanded All Rows" = Table.ExpandTableColumn(#"Merged Queries", "Expanded All Rows", ManualColumns, ManualColumns) in #"Expanded Expanded All Rows"
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