Change column type if column header is number (from text)

Power Query
1

Some column headers are numbers eg 1, 24, 6798… There is an unknown number of these columns but there are also text columns eg “Filename”, “Location” etc. How can I transform column types which are numbers to type number?

I have tried different ways to output a boolean by testing the header text:

All errors prevent the if function from executing. So I added a try otherwise function and it had an error Record to Value or something. Do you know how I can test each column for boolean value

i.e Can the header be converted to a number? If yes type number else type text

See attached PBIX model and transform query:

sample_dynamic_cols.pbix (19.4 KB)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSUQKijNScnHwgXZxZBCSNTEGEGZCwVIrViQYpKEhNhKhEoNhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Soup = _t, #"For " = _t, You = _t, #"1" = _t, #"2" = _t, #"3" = _t]),

    // type change to be dependent whether column headers can convert to number
    TODOconst = "2",
    // example of Bool from try working ok
    TODOtester = try Number.From(TODOconst) > 0 otherwise false,
    
    // @TODO LOGICAL TRY Number.From or Value.Is
    types = Table.TransformColumnTypes(
        Source, List.Transform(
            Table.ColumnNames(Source),
            // Now bool doesn't work within if statement?
            each if try Number.From(_) > -1 then {_, type number}
            else {_, type text}
        )
    ),

    // @TODO LOGICAL TRY Number.From or Value.Is
    types2 = Table.TransformColumnTypes(
        Source, List.Transform(
            Table.ColumnNames(Source),
            // Now bool doesn't work within if statement?
            each if Number.From(_) then {_, type number}
            else error {_, type text}
        )
    )
in
    types2
2

Hi @izzleee,

Good question! This is a fun one, paste this into a new blank query.

let
    ColNames = Table.ColumnNames(Source),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSUQKijNScnHwgXZxZBCSNTEGEGZCwVIrViQYpKEhNhKhEoNhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Soup = _t, #"For " = _t, You = _t, #"1" = _t, #"2" = _t, #"3" = _t]),
    ChType = Table.TransformColumnTypes( Source,
        List.Zip(
            {
                ColNames,
                List.Transform( ColNames, each Value.Type(Value.FromText(_)))
            }
        )
    )
in
    ChType

.
I hope this is helpful

2 Likes