Create two columns from one column breaking out numbers from text

[CharlotteCity12]

Working with a data set where one column consists with either number or text ( see below). How can I separate the text from number in different column? I have tried “column example” /changing data type and several other ways but still can’t figure this one out. Any help will be appreciated. Thanks in advance.

Current Data

Concentration/Null Code

1.08

0.9

0.6

AN

5

AF

Desire Data:

Concentration/Null Code                           Concentration          Null Code

1.08                                                         1.08

0.9                                                           0.9

0.6                                                           0.6

AN                                                                                            AN

5                                                              5                             

AF                                                                                            AF

0.2                                                           0.2                                                                                          

---------------------------------------------------------------------------------------------------------

[Vera_33 · Accepted solution]

Hi @CharlotteCity12 

How about adding columns instead?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzsFCK1YlWMtCzhNJmYNrRD0yZQjhuSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Concentration/Null Code" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Concentration", each try Number.From([#"Concentration/Null Code"]) otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Null Code", each if [Concentration] = null then [#"Concentration/Null Code"] else null)
in
    #"Added Custom1"

[CharlotteCity12 · Accepted solution]

Thank you @Vera_33 This worked perfectly. 

[Vera_33 · Accepted solution]

Hi @CharlotteCity12 

How about adding columns instead?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzsFCK1YlWMtCzhNJmYNrRD0yZQjhuSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Concentration/Null Code" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Concentration", each try Number.From([#"Concentration/Null Code"]) otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Null Code", each if [Concentration] = null then [#"Concentration/Null Code"] else null)
in
    #"Added Custom1"