Forum Discussion
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 val...
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}))
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}))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.