Cannot covert a 9.99e+11 value to integer

[RandyB]

I've imported key integer field from a SQL database and the value is 999000024011. The Grid is showing the imported value as 9.99e+11 in this as well as the subsequent fields. The susequent fields hold similar, but different values (as they are key values). I am familiar that this indicates this is a value of 9 plus 11 placeholder values after the decimal.

I can see the actual values in the lower portion of the query screen. In Excel, this is typically rectified by expanding the column width and Excel formats this value automatically. In this case, expanding the width of the BI column does not change anything. 

How can this be (or should this be something) which can be changed to show the correct value in the grid? I have tried the various formatting methods shown in the Number Column formats including attempting to create a custom column (without success) to multiply the value by 11 (which provides me with an incorrect value as this should be raised to a value with 11 zeros not by 11 times). 

I do have several columns in the database similar to this. 

Thank you in advance for your assistance. 

[alanhodgson]

Hey @RandyB.

You are working in PBI Desktop correct?

I am unable to replicate the issue. I added a "key" column that is 11 numbers long to my sql server and imported into PBI Desktop and if I make the data type "Whole Number" then it shows the field just fine in all my visuals. Can you provide a screenshot of your data or some similiar sample data so we can get a better idea of the problem?

Thanks,

Alan

[RandyB]

Thanks Alan. It is in PowerBI for Desktop. I also have it set as a whole number. You can see from the above, the DOC_ID field is the culprit. 

thank you.

[parry2k]

For the purpose of testing, can you change the column type to text in Query Data modelling.

[RandyB]

Thanks Parry. It did change it to show the value but when I converted it back to a whole number, it converted it back to the exponential format. this will be a problem when I join the tables on the  fields. 

That is a great suggestion. 

Randy.

[parry2k]

Until someone provide what is the exact solution, can you change datatype to text in both tables and join on that, that will get you going.