Forum Discussion
Floating point error when importing data
So I do understand where a floating point error comes from and stuff. But I can't fix it. The problem exist as we are using the American numbers with the '.' as a decimal. Some of our product code are written as ###.### (Where # is a number between 0 and 9). When I import them to Power BI, some are imported with the correct format, other will get something like ###.###00000000000#.
These article codes are set to number as soon as they reach the 'navigation' step. Then it is already too late.I tried several ways to get around this. I changed my formal cells in the source file, which is Excel, and change that to text. Didn't work. I disabled the check box that Power BI give an estimate on the data type of a field. Didn't worked either. Tried to change the data type to text via Power Query in the hope they would change back, still nothing.
Some have .###, other have .#, .## or other combination, so by default correcting .### gets tricky. Also, some are .#0...0#, other are #9...9#. I was hoping someone might know a solution for this problem.
Someone at the office found the answer for my problem. Apparently to highlight the Excel cell with format 'text' is not sufficient enough to set it as text. You first have to trim it. Then this green triangle appears in the corner. When you import that data, it will remove it.
Perhaps this will also solve similar issues for others.
7 Replies
Hi DouweMeer
It seems you may check the 'locale for import'.Here are the articles about it.
https://www.qumio.com/Blog/Lists/Posts/Post.aspx?ID=50
Regards,
Cherie
v-cherch-msft , some product codes that are like numbers, will be imported correctly. To my opinion, it has nothing to do with the decimal to be a ',' or a '.'. Main reason besides why I can't change this, is that the company works with the '.' decimal for numbers as well.
Got any other suggestions?
Hi DouweMeer
I cannot reproduce the same issue.Could you provide the .pbix file and the data for us to test or show some screenshots about your issue?You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,