CODEWITHSUNDEEP
decimalpowerbiroundingpowerquerym
Fred
Fred

Reputation: 43

Power query rounding incorrect, does not round up or down correctly

I have two custom columns at the end of the query named "Total" and "GST", the "Total" column sums columns "Storage", "PltHire", "Admin", "InActivity", "OutActivity", "Add Activity", "Freight", "Packaging" and "Other". While the "GST" Column is 10% of the "Total" Column, I have used the round function in power query and set it to 2 decimal places and for some reason the "GST" column is not rounding correctly. If you review the image it shows that the highlighted row has $154.25 which power query has rounded to $15.42 this should actually round to $15.43. I'm not sure if I have done anything that I need to before hand so if anyone can help me that would be great and if you need anymore information please let me know.

Note - what I found odd was that when I created another custom column for the total column which added the total and gst ([total]*1.1 instead of [total]*0.1) that column was able to round correctly?

Thanks!

I have tried to change the M code by using the following but to no avail.

  1. Number.Round(value as nullable number, digits as nullable number, roundingMode as nullable number) as nullable numberenter image description here

Upvotes: 1

Views: 4538

Answers (2)

ScotlandTheBrave
ScotlandTheBrave

Reputation: 1

Ken Puls discussed this back in Sep 2014:

I do not think it has been fixed so Ken's article is still valid.

You will want to use RoundingMode.AwayFromZero if you want to match how Exel handles rounding:

=Number.Round([Value],2,RoundingMode.AwayFromZero)

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40264

There isn't a uniform consensus on rounding 0.5. In fact, rounding to the even (Power Query default) is less biased than rounding up. Fortunately, the Power Query Number.Round function allows for different rounding styles as the optional 3rd argument.

You likely want Number.Round([Custom],2,0) or, equivalently, Number.Round([Custom],2,RoundingMode.Up) but you have the following options as well:

  • RoundingMode.Down (or 1)
  • RoundingMode.AwayFromZero (or 2)
  • RoundingMode.TowardsZero (or 3)
  • RoundingMode.ToEven (or 4) [default]

Upvotes: 2

Related Questions