Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
@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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |