Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Pikachu-Power
Impactful Individual
Impactful Individual

Transformation problem / need comma instead of dot

Hello.

 

I am loading Bing Ads data with the external tool windsor.ai.

 

For spend i get values like 1.43 and so on... to change that field from text to a number power bi uses Table.TransformColumnTypes {"spend", Int64.Type}. But than I get a wrong value 143. When I change 1.43 to 1,43 it stays at text. 

 

How can I solve this problem in the power query editor?

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

The function Table.TransformColumnTypes allows you to explicitly specify what culture to use to interpret the numbers rather than relying on whatever regional settings the program is set to. Try adding "en-US" as an argument if 1.43 should be interpreted as 1 + 43/100.

View solution in original post

7 REPLIES 7
Pikachu-Power
Impactful Individual
Impactful Individual

@AlexisOlson 

 

= Csv.Document(Web.Contents("https://connectors.windsor.ai/bing?api_key=[your API key]&date_preset=last_14d&fields=account_name,campaign,clicks,spend,impressions,date,&_renderer=csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None])

 

Thats the whole Query. In next step i use Table.ReplaceValue to replace dot with comma. I tried to change Encoding to Europe and so on but I think there is not the problem.

Hmm. I wonder if it's getting the separator commas confused with commas that are part of the number string.

 

Can you share a small CSV example that's a similar format to what the API returns? It's hard to debug without concrete examples.

Sorry i have missed one thing. Adding "en-US" helped now. Int64.Type was the wrong typeTransformation. I changed it to number. 

 

AlexisOlson
Super User
Super User

The function Table.TransformColumnTypes allows you to explicitly specify what culture to use to interpret the numbers rather than relying on whatever regional settings the program is set to. Try adding "en-US" as an argument if 1.43 should be interpreted as 1 + 43/100.

Settings are Europe/Germany. I need 1234,56 with comma as a decimal separator. But in import I get 1234.56 which is changed than to 123456.

 

The Web Link from Windsor contains an Encoding part... does someone know for what it stands and maybe it is possible to control that via Encoding? 

 

= Csv.Document(Web.Contents("https://connectors.windsor.ai/bing?api_key=[your API key]&date_preset=last_14d&fields=account_name,campaign,clicks,spend,impressions,date,&_renderer=csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None])

 

@AlexisOlson 

Adding "en-US" helps a little bit but changes 9.88 to 10. Some idea to change it to decimal 9,88? 

 

PS:

One workaround I found out: REPLACE dot with comma. Then use type decimal. But it would be interessting to know if it is possible to load it directly right into power bi.

Can you share your full query? It's strange to me that it's converting to an integer unless you've specified that.

ToddChitt
Super User
Super User

What do your regional settings indicate? What is the decimal separator? If your number format is like in USA, it would be something like: 1,234.56 so converting that to INT yields 1234. But if your number format is different, like: 1.234,56, then you may get a different answer. 

Try this: Strip out anthing to the RIGHT of your decimal separator, whatever that is. Then REMOVE the thousands separator, whatever THAT is. What is left should be a pure integer.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.