Forum Discussion

nelsonwhyu's avatar
nelsonwhyu
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...
Need Help
  • artemus's avatar
    artemus
    6 years ago
    = Table.AddColumn(Source, "Key2", each Text.Combine( List.Transform(Level_List, (col) => Record.Field(_, col)), "__")  )
Anonymous's avatar
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's avatar
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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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"