Forum Discussion

RandyB's avatar
RandyB
New Member
9 years ago

Cannot covert a 9.99e+11 value to integer

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. 

 

 

Need Help

5 Replies

  • alanhodgson's avatar
    alanhodgson
    Solution Supplier
    9 years ago

    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

     

     

    • parry2k's avatar
      parry2k
      Super User
      9 years ago

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

      • RandyB's avatar
        RandyB
        New Member
        9 years ago

        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.

    • RandyB's avatar
      RandyB
      New Member
      9 years ago

       

      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.