Forum Discussion
Concatenate Dynamic COlumn
= Table.AddColumn(Source, "Key2", each Text.Combine( List.Transform(Level_List, (col) => Record.Field(_, col)), "__") )
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")
?
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.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"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"