Forum Discussion

freelensia's avatar
freelensia
Advocate II
6 years ago

Function to update any table with new values from another table

This is just an idea out there for discussions...I imagine this kind of function would be incredibly useful for many people out there.

 

Imagine I have table A, of 10 columns and 100,000 rows, we call it DictionaryTbl. I also have table B, of the same 10 columns but only 10 rows, we call it UpdateTbl. UpdateTbl contains some new values and some existing values when compared to DictionaryTbl. The ID column is the unique key for both tables.

 

This magical function would check each record in DuplicateTbl against its counterpart in DictionaryTbl. If any column value has changed, update that value in DictionaryTbl.

 

In my mind, the "normal" (boring) approach would be to:

1. Merge DuplicateTbl to DictionaryTbl, keeping on records of DictionaryTbl and only those matching from DuplicateTbl

2. Check each col from DuplicateTbl against its counterpart in DictionaryTbl. If the value is different, replace DictionaryTbl with the value from DuplicateTbl

3. Remove all DuplicateTbl columns

 

Of course like most elegant functions, this needs to work with any table names.

 

For simplicty, let's assume both tables must have exactly the same number of columns, and the columns are in the exact same order. If you have a solution that works with fewer columns in DuplicateTbl, and/or irrespective of column orders, you are a genius.

Anybody even think of attempting something like this? Mariusz ImkeF MarcelBeug 

Need Help

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

       

       

      • thowa's avatar
        thowa
        Regular Visitor
        6 years ago

        @jborro 

        (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