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.
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"Thanks AlexisOlson 🙂
- I used a similar method to create a list query that generates a list of column names from the Updateable table and then use List.Skip() to skip the ProductKey column and List.Difference().
- One question: Is it possible to make the column datatype transforms more dynamic for this line?
#"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}}),I tried Table.Schema, and then used the "Name" & "TypeName" but couldnt wrap my head around how to get it in the same format.
SelectColumns = Table.SelectColumns( Table.Schema( ColTypes ),{"Name","TypeName"},MissingField.UseNull),where "ColTypes" is the Table.TransformColumnTypes().
P.S: Strange enough, when the query picks up the data types from the Excel Updateable table, most of the datatypes show up as "ABC 123" - though i have formatted the each of the table columns properly. Why is the query not able to recognize the data types from the table?
I don't think you can preserve types when it's loading a table from a spreadsheet, so if you want types, you'll have to have a step for that. However, you could just delete that step entirely and the query should still work.
P.S. I think I should have excluded "Version" from ColumnNames in my query.
I think with the merge the versions column wasn't present.
One question:
Incase there are already duplicate rows in the Updateable table, this does not seem to work!
for e.g in my real data, there are duplicate ProductKey rows where one row may have the manual columns filled in, and the other row may have nulls (blanks).
How to handle this situation in the `Updateable` table in the initial stage itself, before we combine it with `NewData` table?