A very detailed discussion today about how Excel rounds. We were all taught in school that 8.4 rounds to 8 and 8.5 rounds to 9. But not in Power Query.
Without letting you know, Power Query defaults to a rounding mode known as ASTM-E29 rounding or Banker’s Rounding. In this scenario, anything ending in 5 rounds to the even number!
I am joined by Excel MVP Celia Alves today. Read Celia’s great blog post on this topic at https://solveandexcel.ca/2021/03/09/rounding-in-power-query-default-rounding-mode-and-the-binary-decimal-conversion-issue-in-excel/ Also subscribe to Celia’ channel for a free weekly Excel course: https://www.youtube.com/channel/UCzWFp4hOnIBHHCJi_SEWV7g

Table of Contents for this video.
(0:00) Introduction
(1:00) Power Query (by Default) is Rounding Differently than Excel
(7:09) Situations where you may not want Banker’s rounding
(8:12) How Power Query defaults to Banker’s Rounding
(9:42) How You Can Fix the Problem by Editing the M Code in Power Query and adding an optional 3rd argument of RoundingMode.AwayFromZero to the Number.Round argument.
(12:22) Currently, the Power Query Documentation Isn’t explaining this Well
(14:25) Number.ROUND(Value,2,RoundMode.AwayFromZero) doesn’t even match Number.RoundAwayFromZero
(14:43) It’s okay with Bill if Power Query is better than Excel, but tell people that it is different
(17:00) The 17-digit precision BUG strikes a third time, this time in Celia’s workbook when Power Query reads a closed Excel file.
(23:30) Subscribe to Celia’s channel for a free hour-long Excel class every week!