Forum Discussion
Concatenate Dynamic COlumn
Hi,
I am trying to create a new Column "Key" by concatenating every column with header starting with "Level ". However, number of Levels are dynamic and varies across my source files.
| Level 1 | Level 2 | Level 3 | Key |
| A | B | C | A__B__C |
| D | E | F | D__E__F |
| A | E | F | A__E__F |
As I won't know in advanced how many Level columns are there, I have a function that detects them and store in a List variable called Level_List.
My question is if there's a way to replace the hard-coded column names (highlighted in red) in the following formula by my variable Level_List?
= Table.AddColumn( Source, "Key2", each Text.Combine( {[Level 1],[Level 2], [Level 3]}, "__") )
Doing this: = Table.AddColumn( Source, "Key2", each Text.Combine( Level_List , "__") )
gives me Level 1__Level 2__Level 3 for each row which contenate the columns headers as Text rather than the values stored within.
Thanks!
Nelson
= Table.AddColumn(Source, "Key2", each Text.Combine( List.Transform(Level_List, (col) => Record.Field(_, col)), "__") )
10 Replies
Thanks Anonymous for looking into this!
However,
cols=List.RemoveLastN(Table.ColumnNames(Source),1)
removes the last column but since I have no control of the source file, so the "Level " columns are not necessarily adjacent to each other nor are they always a fixed number of columns from the right.
Also,newKey=Table.AddColumn( Source, "Key2", each Text.Combine( cols, "__") )
will concatenate columns names instead of values in each column so that will result in Level 1__Level 2__Level 3 for each row instead of
A__B__C
D__E__F
...sorry. I completely misunderstood the problem.
What about this:
Table.CombineColumns(Source, List_Level, each Text.Combine( _ , "__"), "key2")
?
= Table.AddColumn(Source, "Key2", each Text.Combine( List.Transform(Level_List, (col) => Record.Field(_, col)), "__") )Thanks artemus ! This works like magic
Glad it works for you.
Is there some part of this formula that you don't understand?
