If Otherwise error

[Anonymous]

Hi,

I have below line:

= Table.TransformColumnTypes(#"Filtered Rows",{{"userId", Int64.Type}})

this changes column to whole numbers, on some lines this drops errors. I have tried below

= try Table.TransformColumnTypes(#"Filtered Rows",{{"userId", Int64.Type}}) otherwise null

but it doesn't work. It still shows errors.

What I would like achieve is:

convert to whole numbers, if error leave as text

thanks

dan

[Jimmy801 · Accepted solution]

Hello @Anonymous 

this is because Table.TransformColumnTypes is applied at once. When you get an error means that the content isn't suitable for the change. However this means the column type can't be changed - only on cell level. This can be achieved by applying a TransformColumns only. See here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmAyMSkZTOvqgqmS1OISpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t]),
    TransformColumn =Table.TransformColumn
    (
        Source,
        {
            {
                "Number", each try Number.From(_) otherwise _, type any
            }
        }
    )
in
    TransformColumn

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

Hi,

thanks for explanation, much appreciated.

dan