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

     

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. 

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

      That looks doable, but I need some data to work on.

      Could you please paste a link to a file with sample data or at least paste the sample data into the thread so that I can easily copy it.

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

        ImkeF I've made a sample with the same type of data that I sent previously:
        Link: https://drive.google.com/file/d/15y2J3Ke4nHyLal9fcGibqjZZvPIUZeBS/view?usp=sharing

        Some comments to provide you a little help:
        1) String to Number: 10.000,53 ; 10,000.53 ; 10000,35 ; 10000.35 = To reespectively 1000053; 1000053; 1000035; 1000035
        (all digits will remain when the string is converted to number).
        So I think it is easier to work before with strings than numbers.

        2) I dont care with "cents" example: 10.000,53 or 10,000.53 can be = To 10000 && 10000 but not 1000053

        3) Last, I did some steps, but the only one that you really need to work is this marked as red in the image bellow:
        Step Necessary
        The only issue is that after the step 3 is ready, I'll probably dont know how to convert it to number without corrupt 10,000 or 10.000 that's why I said I don't care with cents, because it is better to just don't have it in the convertion, otherwise it will be digits.
        Obs: I've added comments inside advanced editor 

        Thank you and good luck.