Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to create or merge specific columns based on other table instructions

I have "TableRAW" that contains many columns that start with "c" and a number.  The rows may or may not have data for those c-numbers.

 

I have a table "TableALIAS" that tells us there are 3 cnumbers that can be given the alias "AmbPress_mbar" and 3 other cnumbers that can be given the Alias "Amb_Air_Temp", and so on.

 

 

What I want is a method that walks through "TableRAW".  We look at row 1 and get the MAX of (c315461, c23618, c16524), if it is not null, we create a new column "AmbPress_mbar" and insert the max.  We then repeat the process for all the "alias_name"s, adding new columns based on matching cnumber MAX values.

 

As we move through all the rows in the table, we repeat the procees.  If the alias_name column already exists, we have a place to put the max.  If it does not exist, we create it.

 

The final table can have all the cnumber columns deleted, so that only the _id, timestamp, and the newly created alias columns remain.

 

Thank you! 

 

 

 

Need Help

3 Replies

  • artemus's avatar
    artemus
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    ok... lets see if I can get this right:

     

     

    = List.Accumulate(List.Distinct(TableALIAS[alias_name]), TableRAW, (current, next) => 
 Table.CombineColumns(current, Table.SelectRows(TableALIAS, each next = [alias_name])[cnumber], List.Max, next))

     

     

    I don't have your exact dataset schema, so hopefully this is close enough to what you want.

     

    Note: This assumes that TableRAW has all the c columns that are in TableALIAS

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    Is this problem sloved? 
    If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
    If not, please feel free to let me know.
     
    Best Regards
    Maggie