Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Split Column to Text Values and Number Values

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!

Need Help

2 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    3 years ago

    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's avatar
    AlB
    Community Champion
    3 years ago

    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.