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

[Anonymous]

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! 

[v-juanli-msft]

Hi @Anonymous 

If my result is what you expect,please check my pbix file and follow steps on the right pane inside Edit queries.

Original sample data:

Final result 1, 2

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

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

[artemus]

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