Forum Discussion

prakhar's avatar
prakhar
Frequent Visitor
9 years ago
Solved

Selecting name fo column dynamically

I have two tables that look like this: Table1 Text | Level a   |   12 b   |   16 c   |   20 Table 2 Item | a | b | c X  | 10  | 8   | 15 Y  |  15 | 16 | 19 Z  |  22 |  18 | 25 I need t...
  • MarcelBeug's avatar
    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"