Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Convert text with commas into value

Hi all,   My database has some numerical fields that come in text and some of them have commas, by the time to convert the text to a value, the comma disappear and my number become  "bigger". For e...
Need Help
  • Chihiro's avatar
    Chihiro
    7 years ago

    Oh, I guess I misunderstood. Then replace "," with "." instead of blank.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

So, are you saying that the commas in your case are decimals? So that 100,00 should be 100.00?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

100,00 should be 100 and 99,85 should be 99,85 and not 9985 for example. The commas disappear at all

  • Chihiro's avatar
    Chihiro
    Solution Sage
    7 years ago

    Oh, I guess I misunderstood. Then replace "," with "." instead of blank.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I've used several formulas to reach my final goal, and I was successfull, thank you guys 

       

      Test = VALUE(if(find(",";Production[PRODUCTION EBITDA YTD];1;0)>0;replace(Production[PRODUCTION EBITDA YTD];find(",";Production[PRODUCTION EBITDA YTD];1;0);1;".");Production[PRODUCTION EBITDA YTD]))

       

      Best regards

    • Avadobreva's avatar
      Avadobreva
      New Member
      4 years ago

      This one comment saved my life today. Thanks man! 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    These are always tough because I really can't play with the regional language settings which I believe handle things like decimals and dates in different formats. Sounds like your's might be set to English perhaps? Seems like if you had the right regional settings that Power BI would recognize those commas as decimals and convert them correctly to numbers by just changine the type.