Forum Discussion

gafoorgk's avatar
gafoorgk
Frequent Visitor
1 year ago
Solved

Calculated column in Excel Power Query is showing very small difference from expected value

Hi,

 

I have a Query in Excel Power Query, where I created a calculated column from 3 other columns in the query. Strange thing is when I calculated manually by copying values from 3 columns, I get result -94.07. But the same calculation in a calculated column is showing value as -94.069999999999709. Column cell is showing value right (-94.07), but once the cell is clicked I see value in detail panel below as -94.069999999999709.

 

Though it's not giving any problem in what I wanna achieive, just curious to know why is it happening.

 

Thanks,

Show and Tell

3 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi gafoorgk 
    This is due to Excel’s floating-point arithmetic, which can introduce tiny rounding errors. To display exactly -94.07, use the ROUND in Power Query:

    = ROUND([YourCalculation], 2)


    This will ensure consistency between the cell and the detailed view. Hope that helps!

    • gafoorgk's avatar
      gafoorgk
      Frequent Visitor
      1 year ago

      Hi,

       

      Used ROUND function in Power Query already. I was just curious why PowerQuery was bahaving this way and I didn't know about floating-point arithmetic issue.

       

      Involved numbers to get -94.07 is just normal decimal numbers, which works fine in Excel, but the same calculation in PowerQuery got this floating-point arithmetic issue. Strange!!! But anyway, thanks for pointing to the cause of this issue.