Excel Exponential format problem

[Anonymous]

I am tring to load an Excel sheet with a column that looks like this:

Serial

SPF14S4P6

3.56422E+14

3.55624E+14

8.67427E+14

3.53484E+14

Power Bi loads this as text so I lose some digits at the end of each entry due to the exponential notation. I can't expect my client to edit the Excel doc in this case. I've tried other solutions posted here without success. Part of the problem here is that there are some actual text strings present in the column. Is there any way Power Bi can deal with this? Help would be much appreciated!

[MFelix]

Hi @Anonymous ,

Add a new colum and do the following syntax:

try Number.ToText(Number.From([Serial])) otherwise [Serial]

This will test if the value can be converted to number if yes returns the full number otherwise returns the value

Now delete the first column and rename the second one.

It's also possible doing it without adding a new column but you need some advance techniques in power query check the video below you just need to adjust the replace to the formula I have above.

[Anonymous]

@MFelix Thanks, that's a very cool trick! Unfortunately I still lose my last digits, they just turn to zeros. Ie 8,69113E+24   becomes 8691130400900000000000000. 

As mentioned in another reply, I've noticed the problem goes away when open the doc and save as .xlsx. The original is a .xls. The data source is an export from an old software program and exporting as .xlsx is not an option 😞

[MFelix · Accepted solution]

Hi @Anonymous ,

That is a limitation of excel itself as you can see in the link below excel only saves the first 15 numbers, so even if you add a xlsx you would get zeros and not the values.

https://support.microsoft.com/en-us/help/269370/last-digits-are-changed-to-zeroes-when-you-type-long-numbers-in-cells

On the first post you add the exponential was of 14 so everything worked properly, if you are abble to extract in text, csv or similar maybe the issue can be solved.

[mahoneypat]

Do you plan to keep the text and numbers in the same column ultimately?  One thing you could do is duplicate the column, convert one to # and the other to text, replace the errors with null.  That way you will have a column with #s for calculations and another column with your text values.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat Thanks very much for your reply. Unfortunately I still lose my last digits, they just turn to zeros. Ie 8,69113E+24   becomes 8691130400900000000000000. 

I've noticed the problem goes away when open the doc and save as .xlsx. The original is a .xls. The data source is an export from an old software program and exporting as .xlsx is not an option 😞