Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Everyone,
I am trying to figure out the following problem: I have a column A of type text with both words and numbers. I would like to create a new column B where I only return from column A the text which includes the numbers 0,1,2.....9.
Can anyone help me out?
Thanks in advance.
Solved! Go to Solution.
Ok then you can try this.
1.
Text.Select([Column1], {"0".."9"})
2.
Text.Select([Column1], {"A".."Z"})
3.
Filter the Row with the letters (null) Then you should get what you want
->
This is quite helpful, thank you. However, in my example column, I have rows either with text and numbers or rows with only numbers and I would like to extract only the rows which contain numbers and ignore the ones where there's a mix between text and numbers (there can appear a blank space). I would be very grateful if you could help me. Best regards
Hey nore755, you can try something like this
if (Value.Is([Column1], Int64.Type)) then [Column1] else ""
This give me just the Numbers.
I hope that work for you.
I tried this but I have noticed this solution does not work for me, because my column type is text, which I cannot convert. I was try something like this, but I am getting errors:
If text.contains (columnA,"0") then columnA
else if text.contains (columnA,"1") then columnA
else if text.contains (columnA,"2") then columnA
...
else ""
It would be of great help!
Ok then you can try this.
1.
Text.Select([Column1], {"0".."9"})
2.
Text.Select([Column1], {"A".."Z"})
3.
Filter the Row with the letters (null) Then you should get what you want
->
Thank you very much for your help, much appreciated!!
Maybe this will help you.
There it is solved in Power Query.
https://community.powerbi.com/t5/Desktop/NEED-HELP-FOR-EXTRACT-ONLY-NUMBERS/td-p/270983
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |