cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mad
Regular Visitor

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 🙂

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

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!

mahoneypat
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Mad
Regular Visitor

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors