Concatenate Dynamic COlumn

[nelsonwhyu]

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

[artemus · Accepted solution]

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

[nelsonwhyu]

Thanks @artemus ! This works like magic

[artemus]

Glad it works for you.

Is there some part of this formula that you don't understand?

[nelsonwhyu]

@artemus , thanks it's very helpful. The formula all makes sense.

I think I just need to get more fluent in the usage/meaning of "each" and  "_"  notation

[Anonymous]

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]

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]

@nelsonwhyu 

sorry. I completely misunderstood the problem.

What about this:

Table.CombineColumns(Source, List_Level, each Text.Combine( _ , "__"), "key2")

?

[nelsonwhyu]

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

[Anonymous]

@nelsonwhyu  I was just giving an hint to to overcome the main problem

If you need complete solution, probably is like this:

newKey=Table.CombineColumns(Source, level_list, each Text.Combine(_, "__"), "keytwo"),
#"Merge di query eseguito" = Table.NestedJoin(SourceI ,{"Indice"},newKey,{"Indice"},"newKey",JoinKind.LeftOuter),
#"Tabella newKey espansa1" = Table.ExpandTableColumn(#"Merge di query eseguito", "newKey", {"keytwo"}, {"keytwo"}),
#"Rimosse colonne1" = Table.RemoveColumns(#"Tabella newKey espansa1",{"Indice"})
in
#"Rimosse colonne1"

[Anonymous]

Another way to get what you want is to do in the reverse sense what done: 😁

#"Duplicata colonna" = Table.DuplicateColumn(newKey,"keytwo", "keytwo - Copia"),
#"Suddividi colonna in base al delimitatore" = Table.SplitColumn(#"Duplicata colonna", "keytwo - Copia", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), level_list)
in
#"Suddividi colonna in base al delimitatore"