Floating point error when importing data

[DouweMeer]

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. 

[v-cherch-msft]

Hi @DouweMeer 

It seems you may check the 'locale for import'.Here are the articles about it.

https://docs.microsoft.com/en-us/power-bi/supported-languages-countries-regions#choose-the-language-...

https://www.qumio.com/Blog/Lists/Posts/Post.aspx?ID=50

Regards,

Cherie

[DouweMeer]

@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?

[v-cherch-msft]

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,

[DouweMeer]

@v-cherch-msft 

This is a selection of the items available. Further on in the same file there are related list prices with as decimal seperator the '.'. 

Power BIExcel with text formatThese are numbers in the Excel source file

[v-cherch-msft]

Hi @DouweMeer 

There're two ways for you to try.

First: Change the value format to text in query editor.

Second: Use Decimal Number format and set the number as 3.

If possible,please share a test .pbix file for us to check or submit a support ticket.

https://powerbi.microsoft.com/en-us/support/

Regards

[DouweMeer]

@v-cherch-msft 

I guess that will become a support ticket then :). 

The problem I see is that when I try to change the detect type, it will inserted as a new step. This new step can only placed after the navigation step. On that point, it is already too late. Besides, there are also article codes with .####. These will be changed to .### by your suggestion. Something I don't want to happen either :). 

[DouweMeer · Accepted solution]

@v-cherch-msft 

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.