Forum Discussion
Function to update any table with new values from another table
Yes, I have thought about this and would be surprised if there is no elegant way of doing this very common task! Found your post when looking for a solution. See my more detailed question post here:
Overlaying/merging two tables to add missing data in empty cells and new rows/columns
Hope there is this functionality; looking thorugh huge tables for compare would be a bummer...
Hi freelensia & thowa ,
not sure about the performance on 100s of 1000s rows - Table.Group may be a bit heavy on large datasets, - but this is a function to do what you want (using thowa's data as an example):
(Dictionary as table, Update as table, Attributes as list)=>
let
AttributeFields = Attributes,
ValueFields = List.RemoveItems(Table.ColumnNames(Source), AttributeFields),
ValueFieldFunction = List.Accumulate(ValueFields, {}, (a, n) => a & {{n, (x)=>List.First(List.RemoveNulls(Table.Column(x, n)))}}),
Source = Table.Combine({Update, Dictionary}),
#"Grouped Rows" = Table.Group(Source, AttributeFields, ValueFieldFunction)
in
#"Grouped Rows"Update parameter is the one that takes precedent on changed data (i.e. assumed table 1 in thowa's example).
This relies on the fact that you can direct PQ to put one table "on top" of another when appending.
Attributes parameter takes the "group by" list - a list of fields that you want to match by.
The output needs to the post-processed as the function removes types and shuffle the columns.
Kind regards,
JB
(Deleted prvious response as I noticed that I made an error when trying to apply)
Thanks for the suggestion! Lean piece of code, but I have to admit that I have not yet fully understood it. Will try when I find the time.
Really surprised that there is no stock-function for this tasks - it seems quite common.
When traqying to apply your solution, I don't seem to get the planned result though - same as a Table.Combine except that the column sequence has changed.
I also notice that it only works with fables fully converted to text, else I get "cannot convert to text" errors.
Attributed input must be a list of column names, right (so a list from 1-5 in my eample case)?
I may be doing something wrong, though...
Thomas
Hi Thomas,
Sorry, was not clear in my post - thee attributes list assumes "group by" columns, in your case [Date]. 1-5 are "value" columns.
If you do it this way it should work Ok, in my test 1-5 has "number-type" values, everything works fine. In fact, it doe not matter what type of data is in these columns, it will be removed in any case (a code for adding the formatting back is quite simple anyway). You can even mix formats in the "old" and "new" data, in this case a number can be replaced by a text and vice versa.
This is my code for using the function (assuming it is called fUpdate😞
let Source = fUpdateTable(DictionaryTbl, UpdateTbl, {"Date"}) in SourceKind regards,
John
Anonymous
THANKS!
One table had 2020 and the other 2000 as the years...so obviously they wouldn't combine!
...I noticed that after banging my head against the wal all day!
Thanks so much for following up. Great Code piece.
Thomas
Works fine!
An other alternative approach was posted here:
Overlaying/merging two tables to add missing data in empty cells and new rows/columns
By camargos88
Thanks to both oof you!