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, n...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

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

     

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

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

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

    Nathaniel_C  Thanks for your reply :)

    ImkeF  Hey dude, please give me a hand here hahaha =P

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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?

       

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

        ImkeF  Sorry for the delay, I was sick during these past days. 


        Oh man, you're a genius.
        Yeah, it is not representative, I've uploaded the wrong image, however, the real one is here, as follows:

        Original Data without any transformationAnd the Data with: formula applied from M

        Formula:
        Obs: "Valor - Copiar" and "Valor" are the same data, it is just a duplicated column.

        if Text.Contains([#"Valor - Copiar"], "." ) then Text.BeforeDelimiter ([#"Valor - Copiar"], ",") else Text.BeforeDelimiter ([#"Valor - Copiar"], ".")


        So I dont know how can I change this one to get the right formula , there are cases where im stripping out the entire number because of a number is starting with ",", and of course if a change this formula, will happen the same thing when it starts with ".".

        I really dont know how to do that properly.