Forum Discussion

renanc's avatar
renanc
Icon for Helper II rankHelper II
6 years ago
Solved

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's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion

    renanc ,

     

    Let me suggest that we reach out to ImkeF who is the M master!

     


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion

        Hi renanc ,

        You need a pattern here. Would the 3rd last character be fine (if it's a point/dot, then swap dots and commas else convert to number as it is)?

        Or is the posted data sample not representative?