Split Column to Text Values and Number Values

[Anonymous]

This is slightly different than splitting a column by first instance of text of number.

I have a column whose values are EITHER Text or Numbers.

Eg.
AAA

BBB

123
456

How do I split this into 2 columns, one with the text values, and the other with the number values?

Thanks!

[themistoklis · Accepted solution]

@Anonymous 

You can find a good answer on this topic:

https://community.powerbi.com/t5/Power-Query/Split-column-by-data-type/td-p/1171879

1. Create a duplicated column

2. In the duplicated column change the datatype to number and replace the erros with nulls (this way you will have all the numbers). Number field

3. Create a new custom column where you will add a condition (if there is null in the duplicated column then get the value from the original field (it will get the text)). Text Field

PowerBI workspace attached

[AlB]

Hi @Anonymous 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0VIrViVZycnIC04ZGxmDaxNRMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Text", each if Text.PositionOfAny([Column1], {"0".."9"}) < 0 then [Column1] else null, type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Numbers", each if Text.PositionOfAny([Column1], {"0".."9"}) >= 0 then [Column1] else null, type number)
in
    #"Added Custom1"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.