Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
tkboisvert
Helper II
Helper II

Unexpected modulo behavior

Hi all,
 
In excel, I have this formula:
MOD(-10 + 9,12) + 1
And the expected result is 12
 
However, in PowerQuery the same formula:
Number.Mod(-10 + 9, 12) + 1
Results in 0
 
The strange thing is that for other numbers ( -1 ) I get the same result in both systems..
 
I expect this to have something to do with the nature of MOD, and how I'm using negative numbers.. But I would still like to know which is 'correct'..
 
tkboisvert_0-1632352753075.png

 

 
1 ACCEPTED SOLUTION
KNP
Super User
Super User

Hi @tkboisvert,

 

The way the formulas is calculated is slightly different between the two functions.

Check out this video: https://www.youtube.com/watch?v=K4ImPRsi3vg&ab_channel=ExcelIsFun

 

Kim

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@tkboisvert According to Modulo Calculator (calculators.org), Power Query is correct.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
KNP
Super User
Super User

Hi @tkboisvert,

 

The way the formulas is calculated is slightly different between the two functions.

Check out this video: https://www.youtube.com/watch?v=K4ImPRsi3vg&ab_channel=ExcelIsFun

 

Kim

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Thank you!

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors