Excel Power Query Defaults To Bankers Rounding Featuring Celia Alves - Episode 2392

Microsoft Excel Tutorial: Power Query is not rounding like Excel.

🔍 Dive into the intricacies of Excel rounding in MrExcel Podcast Episode 2392 with special guest Celia Alves, Excel MVP! 🔄 Discover the surprising differences in Power Query rounding modes compared to Excel, and how it can impact your calculations. Join the conversation as Bill Jelen and Celia unravel the mysteries of rounding rules, including the unexpected default Banker’s rounding in Power Query. 🤔 Learn how to control rounding modes in Power Query and ensure precision in your calculations. Plus, explore the fascinating revelation about Excel storing 17 digits of precision and the potential discrepancies it introduces. 📊 Don't miss this eye-opening discussion that could reshape your approach to Excel precision and rounding! 🔍🔄 #MrExcel #ExcelRounding #PowerQuery #ExcelPrecision

🤯 Uncover the hidden world of Excel rounding in MrExcel Podcast Episode 2392! Join Bill Jelen and Excel MVP Celia Alves as they explore the surprising differences in Power Query rounding modes compared to Excel. 🔄 Understand the impact of default Banker’s rounding in Power Query and learn how to take control of rounding modes for precise calculations. 🧐 Dive into the complexities of Excel storing 17 digits of precision and the potential challenges it introduces. Don't let rounding mysteries affect your data—watch this episode for valuable insights! 🎙️📈 #ExcelPodcast #RoundingRules #PowerQuery #ExcelPrecision

🔄 Rethink your approach to Excel rounding with MrExcel Podcast Episode 2392! 🤓 Bill Jelen and special guest Celia Alves, Excel MVP, unravel the mysteries of Power Query rounding modes and their surprising differences from Excel. Explore the nuances of Banker’s rounding by default in Power Query and learn how to take control of rounding modes for accurate calculations. 📊 Plus, delve into the revelation of Excel storing 17 digits of precision and the potential discrepancies it may create. Tune in for a thought-provoking discussion that could transform your Excel precision strategies! 🔍🔄 #ExcelRounding #PowerQuery #PrecisionInsights #MrExcel

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!

Table of Contents
(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) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Banker's rounding in Power Query
Difference between Excel and Power Query rounding
Excel MVP Celia Alves discusses rounding in Power Query
Excel ROUND function vs Power Query rounding
Excel rounding conventions explained
Excel rounding vs Power Query rounding
How to control rounding in Power Query
How to round numbers in Power Query
Learn Excel from MrExcel Podcast Episode 2392
Power Query rounding modes
Power Query rounding options
Rounding mode parameter in Power Query

[rosswaterston]

Great video, rounding is always a pain point and being able to explain what Excel vs Power BI (power query) is different and then fix it is a huge help!

[ExcelTurbo]

Great video! This is an important alert to everyone who handles decimal numbers in Power Query. Well done, Celia Alves and Bill Jelen!

[johndurran]

Thank you Bill and Celia for this fascinating video. I'm just glad I'm not trying to calculate how to send someone to the moon using Excel as they would probably end up in Mars!

[mattschoular]

Thanks Mr.Excel and Celia. This is why we have MVP's like yourselves. There is so much great info in this community....if you know what, and where to search for it.

[joannpaules]

Last winter I had problems with rounding out of Power Query. It drove me bonkers for about a then I learned about Banker's Rounding. Now that I know it happens, whenever I round, I Round.AwayFromZero. ;-)

[patrickschardt]

I must be a huge nerd. I don’t used power query (yet, I just haven’t learned it). I really enjoyed this video. Bigger moral of the story is to pay close attention to your data

[LearnAccessByCrystal]

very interesting -- thanks Bill and Celia

[darrylmorgan]

Roundingmode WOW!!! Mega Interesting Tutorial...Thank You Celia and Mr Excel :)

[jerrydellasala]

Mr. E - I had seen your video a few months back, and when LAMBDA came out created the function BRound "=LAMBDA(num, pres, IF(MOD(num, 1)=0.5, MROUND(num, 2), ROUND(num, pres)))" based on that video! In a test I ran using 1, 000, 000 random numbers with one decimal it worked exactly as your VBA macro and PQ's standard Number.Round function. I would have posted it in the Forum, but I haven't participated enough to post yet ;(.
UPDATE - After watching this I reworked my test using 1M random numbers between 1 and 99 with two decimal places (previously I had only used one).
I found that the VBA Macro, the LAMBDA formula, and PQ Number.Round all gave the same resulting SUM with an error of -0.00007%
compared to the SUM of the two decimal numbers, but using the 3rd parameter in the standard PQ Number.Round (RoundingMode.AwayFromZero) gave an error of 0.00977%
and was 4, 922
off from the other 3 rounding modes!

[DougHExcel]

oh my! I hope I remember the away from zero config...I'll just search for this vid!

[wayneedmondson]

Hi Mr. Excel and Celia. This topic is super interesting and enlightening, as well as important to understand. Thanks for sharing this valuable information :)) Thumbs up!!

[brianspiller]

In the Microsoft PDF document, "Microsoft Power Query for Excel: Formula Library Specification", dated February 2015, the parameter is indicated and the options explained.
The document is no longer available on any Microsoft webset and the community has no similiar replacement available from MS. The onle reference is okay when you know what you're looking for, but I found I discover more when i have paper or similiar pdf to flip through.
Yes, the good old days.

[jeffrey]

THANK YOU!!! this video is a gem! 🤓

Can you make another video of this showing the latest version of the PBI Desktop/QueryEditor thank you again!

[brianspiller]

base10 to base2 conversion. Displayed precision has always been 15 digits. The addition 2 digits have limited precision in decimal and cannot be displayed with confidence.
It is still a problem for PQ to read it differently.

[TSSC]

Super interesting. Thanks and a congratulation to the upcoming 100k subscribers. By the way, check the link yo Celia’s channel … it doesn’t work.

[AnandPenmatcha]

unfortunately, the person who wrote and help files may not know the significance of this rounding

[GrainneDuggan_Excel]

Thank you both. Very interesting and important topic on an issue so easily missed.

Is the 17 digit issue why sometimes you get an error for cross checking calculations i.e. 1.577 = 1.577 gives an Error?

[mattnyman]

Another obscure thing. Do you use the "Use In Formula " feature in the defined names section of the ribbon? How?

[hdati]

Hello, I saw this video and was wondering if you have anything about rounding down to the nearest 15 min, I found that certain times like 2:00 PM and 2 :45 PM roundsdown to 1:45 PM and 2:30 PM.. it seems to work fine for any other time like 2:41 PM but for some reason these 2 time is doing it differently

This is the formula


Thank you for your time

[xosar]

How do you stop rounding all together?