Convert text to number across multiple columns where each column is a mix of text and numbers

try this:

• Set the data type for all the columns to "any"
• For each column, do a Table.Transform which changes values that can converted to numbers as such, otherwise leave them as is.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRMlSK1YlWSklNA7KNwGxDIMsYzDICskCqQGxjIBukKjYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    
//set data type to any
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type any})),

//If they can transformed to a number, do it, otherwise leave it as is
    #"Text Numbers to Numbers" = Table.TransformColumns(#"Changed Type",
        List.Transform(Table.ColumnNames(#"Changed Type"), (cn)=> {cn, each try Number.From(_) otherwise _}))
in
    #"Text Numbers to Numbers"

Before

After