Forum Discussion

Frequent Visitor

6 years ago

Solved

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. Lev...

6 years ago

= Table.AddColumn(Source, "Key2", each Text.Combine( List.Transform(Level_List, (col) => Record.Field(_, col)), "__")  )

Anonymous

Not applicable

6 years ago

maybe this is what you are looking for?

    cols=List.RemoveLastN(Table.ColumnNames(Source),1),

    newKey=Table.AddColumn(  Source, "Key2", each Text.Combine(  cols, "__")  )

in newKey

nelsonwhyu
Frequent Visitor
6 years ago
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
...
- Anonymous
  Not applicable
  6 years ago
  nelsonwhyu
  
  sorry. I completely misunderstood the problem.
  
  What about this:
  
  Table.CombineColumns(Source, List_Level, each Text.Combine( _ , "__"), "key2")
  
  ?
  - nelsonwhyu
    Frequent Visitor
    6 years ago
    Anonymous , yes I tried this before. But this replace the "Level " columns with the newly combined "key2" column, while I still need the "Level " columns so I can use them as Hierarchies in my visuals.
    
    Otherwise, the "key2" columns is what I am looking for.

Related content