Create conditional Table.TransformColumnType in powerquery

Question

I am trying to convert columns to numeric. If TransformColumnTypes causes an error, I want to keep it text. Something like this:

#"Changed Type" = try  Table.TransformColumnTypes(CombineTables,List.Transform(sTranCol, each {_, type number})), otherwise  Table.TransformColumnTypes(CombineTables,List.Transform(sTranCol, each {_, type number})),

Obviously this doesn't work. sTranCol is the list of columns to covert to numeric. It is dynamically created and isn't static. I don't care if it puts error in the cell but transposing with errors in the cells is causing query to abort.

Answer 1

The M Code methods I've seen to detect data type of a column consist of sampling the data and determining the type. This seems messy.

But perhaps an alternative might be type the columns as numeric, and then replace the error values with something that won't cause a problem when transposing.

Here is some sample code to replace errors with null, but you could replace with anything null or numeric:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJBpI6SsZglhGQZQ5mVQBZiWCWKZCVBGaZA1kVEB0ghYYmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
     #"Changed Type" = Table.TransformColumnTypes(Source, 
        List.Transform(Table.ColumnNames(Source), each {_,type nullable number})),
    
    nullList = List.Transform(Table.ColumnNames(#"Changed Type"), each {_, null}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", nullList)
in
    #"Replaced Errors"

Source

Changed Type

Replaced Errors

Edit: Add M Code to set column types depending on if all numeric

let
    Source = Excel.CurrentWorkbook(){[Name="Table37"]}[Content],

//check data type
//if all numbers set to number, else any

colTypes = List.Accumulate(Table.ColumnNames(Source),
    {},
    (state,current)=> List.Combine({state,
    if List.IsEmpty(
            List.RemoveMatchingItems(
                List.Transform(Table.Column(Source,current), each Value.Type(_)), 
                {type number})) 
    then {{current, type number}} 
    else {{current, type any}}})),

    #"Changed Type" = Table.TransformColumnTypes(Source,colTypes)

in
    #"Changed Type"

Source

Changed Type