displayed table values are not matching source data

[Nicho247]

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

[Icey]

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]

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

[lbendlin]

where exactly is the mismatch?

[Nicho247]

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 · Accepted solution]

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