Forum Discussion

AJose's avatar
AJose
Helper I
7 years ago

Can i select the columns with Table.SelectColumns based on a condition - M Code?

I have a parameter which decides which column to select from a table. If the parametet value is "A", "Column1" should be selected and if the parametet value is "B", "Column2" should be selected . Can I do this with Table.SelectColumns in M code?

Need Help

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    7 years ago

    Yes. You need to convert the A to 1, B to 2, etc. You can do that with the following assuming your letter is in the Code column:

    =Character.ToNumber([Code])-64

    The following M code imports a 9 column Excel table, adds the Char Code (above) then gives you the column name of the column referred to by the code.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Column A", type text}, {"Column A2", type text}, {"Column A3", type text}, {"Column A4", type text}, {"Column A5", type text}, {"Column A6", type text}, {"Column A7", type text}, {"Column A8", type text}}),
    #"Added Char Code" = Table.AddColumn(#"Changed Type", "Char Code", each Character.ToNumber([Code])-64, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Char Code", "Relative Column Name", each Table.ColumnNames(#"Added Char Code"){[Char Code]})
in
    #"Added Custom1"