Forum Discussion
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...
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
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. |