Forum Discussion

Mad's avatar
Mad
Regular Visitor
5 years ago

Transforming a text value into a number with Power Query

Hi,

 

I have an issue with Power Query since a few days regarding number formating.

 

We export a data from Google Adwords in a CSV, then work on the CSV from within Power Query to build our reports. 

 

For instance, I will see 91.31€ as a conversion value directly in the interface, while hoping to see 91,31€ in my PBI Report since I am based in Europe. Normally that works fine with the automatic data recognition, but now the CSV itself contains the formating issue. The problem comes in when the data is downloaded. I have no issue in PBI for other sources in CSV.

 

In Power Query, the default value for this is text. When I change it to decimals, I get 9131. I tried to use the function "change type" and to set it up directly as a currency for my country there, but it delivers the same result. 

 

Is there any way to replace points by commas automatically before I switch from text format to currency?

 

Thanks in advance 🙂

Need Help

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You should be able to add a Replace Values step while it is still text type to do that, before you change to decimal.

     

    Pat

     

    • Mad's avatar
      Mad
      Regular Visitor
      5 years ago

      Ok, but now I see that it won't work like this in all cases due to the format ğŸ¤”. I have results originally appearing like this in the CSV: 9783.115.489,02. In Google, I see 97,83€. 

      I suppose it has to do with the regional set up, but can't find any other way to adapt it than by using "change type". It should actually work like this, but for some reason doesn't.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  Mad ,

     

    Try to replace the "." for a "," and see if that helps.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!