Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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.
= 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.
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])
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.
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.
Proud to be a Super User! | |
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
7 | |
6 | |
6 |