Forum Discussion
Helper IVFormat Number not accurate
Hi, I am currently having an issue with a number value not being recognised.
I have a numeric value, pulled with decimals, and when I try to convert it to either a whole number or a decimal number it doesn't provide the right number.
While the source data pull the general number with decimals, the power query doesn't convert the number correctly for either decimal or whole number.
Any suggestion?
You'll have to investigate a bit further-> "Regional settings is default" . Have a look at the Windows Regional settings, specifically the decimal separator.
Can you test a simpler power query example please? Maybe by duplicating the query, deleting steps to get back to the 'changed type' step ->and changing the data type of 'ext price' to Text then to decimal.
--
You could also experiment by duplicating the column, replacing '.' with ',' and then changing data type to decimal. If that works, it would point to the decimal separator.
I'll wait to hear back from you once these things are done.
5 Replies
- HotChilli
Community Champion
My educated guess is that the "." is not being treated as a decimal separator. Maybe due to regional settings or powerbi settings. Can you check this please?
Could you also post the Advanced Editor power query code so that I can replicate what you are doing please?
alexcatalaHi HotChilli
also as mentioned the language displayed on Regional settings is default and Query steps as use application language
Let me know if you have any suggestion
- HotChilli
You'll have to investigate a bit further-> "Regional settings is default" . Have a look at the Windows Regional settings, specifically the decimal separator.
Can you test a simpler power query example please? Maybe by duplicating the query, deleting steps to get back to the 'changed type' step ->and changing the data type of 'ext price' to Text then to decimal.
--
You could also experiment by duplicating the column, replacing '.' with ',' and then changing data type to decimal. If that works, it would point to the decimal separator.
I'll wait to hear back from you once these things are done.
- alexcatala
Hi HotChilli
I replaced the "." with "," and automatically was converted to Text, then change the format to Decimal and it worked.
Thanks a lot!
- HotChilli
OK, short-term that has worked. Does it solve the issue long-term? Maybe you live in a country (and your PC is set up) where '.' is not the decimal separator.