Forum Discussion
Selecting name fo column dynamically
- 9 years ago
It is a bit confusing if you want the values from Table 2 in the new column or if you want the name of the new column to be the concatenation of those values (as the topic title suggests).
Anyhow, the following code does both (it's Power Query code in an Excel workbook; Table 2 was already loaded in PQ with connection only). It's basic code without any checks and/or error handling:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}, {"Level", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x) => Table2[Item]{List.PositionOf(Table.Column(Table2, x[Text]),List.Min(List.Select(Table.Column(Table2, x[Text]), each _ > x[Level])))}), #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", Text.Combine(#"Added Custom"[Custom])}}) in #"Renamed Columns"
It is a bit confusing if you want the values from Table 2 in the new column or if you want the name of the new column to be the concatenation of those values (as the topic title suggests).
Anyhow, the following code does both (it's Power Query code in an Excel workbook; Table 2 was already loaded in PQ with connection only). It's basic code without any checks and/or error handling:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}, {"Level", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x) => Table2[Item]{List.PositionOf(Table.Column(Table2, x[Text]),List.Min(List.Select(Table.Column(Table2, x[Text]), each _ > x[Level])))}),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", Text.Combine(#"Added Custom"[Custom])}})
in
#"Renamed Columns"