Forum Discussion

dmned-ph's avatar
dmned-ph
New Member
3 years ago
Solved

changing column data type based on the suffix in the column name

I have a query template that returns results from sources wich can differ in the number and names of the columns. For example,, the columns may be thus:

 

time

value.1.a

value.1.b

value.2.a

value.2.b

value.3.on

...

 

The results always return a column named 'time' that I can easily convert to a Date/Time data type. The query results always produce some quantity of values with the 'a', 'b' or 'on' suffix, but the number and prefix names can vary. I am looking for a method to change the data type of all the columns with the 'a' or 'b' suffix to a decimal number and the columns with the 'on' suffix to a True/False (boolean) type.

 

Is there a method to select the columns by their suffix and then apply a transformation based on the selection?

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    Not entirely foolproof, but more checking can be added if necessary:

     

    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each 
            let 
                split = Text.Split(_,"."),
                types = if _ = "time" then type time
                    else if List.Last(split) = "a" then type number  
                    else if List.Last(split) = "b" then type number 
                    else if List.Last(split) = "on" then type logical else type any
                in 
                    {_, types}))

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    3 years ago

    Not entirely foolproof, but more checking can be added if necessary:

     

    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each 
            let 
                split = Text.Split(_,"."),
                types = if _ = "time" then type time
                    else if List.Last(split) = "a" then type number  
                    else if List.Last(split) = "b" then type number 
                    else if List.Last(split) = "on" then type logical else type any
                in 
                    {_, types}))
    • dmned-ph's avatar
      dmned-ph
      New Member
      3 years ago

      Thank you...this worked exactly as expected for my query, and it allowed me to learn how to use a nested LET statement. The only tweak I made was my time column includes the date, so I changed the type to datetime.