Forum Discussion

Nicho247's avatar
Nicho247
Helper III
6 years ago
Solved

displayed table values are not matching source data

Hello -

 

Difficult problem to describe.

 

I have a SQL query which is returning some data. In the SQL query I have cast the column in question as a decimal(18,2). I have then pushed this into excel via a database connection. I have then pushed this file into power bi desktop. For a transform i set the data type to fixed decimal number. When I create a table in power bi, the values displayed do not match the excel file nor what is returned in sql. Below is what is copy/pasted. However here is a screenshot. What is going on?

 
 

 

SQL = -153548.08

Excel =  $(153,548.08) (formatted as accounting)

Power BI = -153548.08

Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    That's the way decimals work. apply formatting to it if you want to display only two significant digits.

5 Replies

    • Nicho247's avatar
      Nicho247
      Helper III
      6 years ago

      the screenshot is what is displayed visually. but if i copy/paste it from power bi...it is correct.

       

      screenshot reads

      -153548.0799999999999

      actual value is

      -153548.08

       

      N

       

      • lbendlin's avatar
        lbendlin
        Super User
        6 years ago

        That's the way decimals work. apply formatting to it if you want to display only two significant digits.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Nicho247 ,

     

    Is this problem solved?


    If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.


    If not, please let me know.

     


    Best Regards,
    Icey

    • Nicho247's avatar
      Nicho247
      Helper III
      6 years ago

      Hi Icey 

       

      Yes, though I am dissatified with the explanation. If formatting is set to auto, power bi may or may not convert 2 decimals to 10 decimals without cause. I fixed this by changing from auto to '2' even though the raw data is fixed decimal number & was fixed at 2 upstream through SQL server.

       

       

      N