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

[dmned-ph]

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?

[ronrsnfld · Accepted solution]

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]

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.