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"
Hi prakhar,
You create Table2 using Table3? Could you please share your sample data for further analysis?
Best Regards,
Angelia
Hi v-huizhn-msft,
I had not created it directly. It involved more steps. I just posted the step I needed help with. I posted it in a separate post since its answer did not need the first two tables. I got the answer from that post and the solution by MarcelBeug worked perfectly. Thank you both for your help.