Forum Discussion
String to Number - Multiple Conditions
Hey guys, I want to model a data which is string and turn it to number, however there are some details who might be hard to treat.
The data comes from a chain of strings, like adresses, zip codes, names, and for some rows in this same column I have related values (currency), the ones that I need.
- What I did?
1) First Step, I've changed everything to number, as this image below:
String to Number
And if this wasnt a number it's turned into "null/blanc"
2) I have created a conditional to extract "." and "," - By the image:
Extracting "." and "," from numbers
Why am I even trying to do this instead of just transform into number?
Because I have numbers with multiple conditions whose need to be trated, I'm going to clarify through the other image:
Left column is raw data (string) and the right is string to number over these conditions.
As you can see I have conditions which the interval between numbers starts with "." and "," even there are numbers/strings which have these conditions in the end.
So to resume: Numbers Starting with "." "," and finishing with it.
What I need?
A condition in (M) power query that can complement or change the previous formula that I've created in order to do these conditions, this is related to formula created in image of item: 2).
Thank you guys for your time and help!
Hi renanc ,
your data was a bit dirtier than expected and I realized it a bit late. So the code might not be ideal, but it seems to work (see attachment at the end of this message).
Didn't clean it up, as I had to spend more time than expected already...
9 Replies
- Nathaniel_C
Community Champion
- renanc
Helper II
Nathaniel_C Thanks for your reply :)
ImkeF Hey dude, please give me a hand here hahaha =P