1

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
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
Fred
  • 43
  • 2
  • 8
  • Why is $15.43 more correct than $15.42? – Alexis Olson Apr 01 '19 at 22:22
  • Hi Alex, thanks for your comment. I need it to use the general rules for rounding so 0,1,2,3 or 4 needs to round down while 5,6,7,8 or 9 needs to round up. This is to ensure that I am able to reconcile between two systems and they need to be exact. I did a sanity check in an excel formula to see what the result was so by using the formula "=ROUND($15.425,2)" I would get $15.43. – Fred Apr 01 '19 at 23:07

2 Answers2

2

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]
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
0

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)
Masoud Rahimi
  • 5,785
  • 15
  • 39
  • 67
ScotlandTheBrave
  • 1
  • 2