Int64.From: RoundingMode

[Chihiro]

Can those of you that have PowerQuery test following? I'm seeing unexpected result when using Int64.From() with third argument for RoundingMode.

In sheet:
1. List numbers from -2 to 2 increment number by 0.25 each row (header: Num).
2. In next column enter =INT(A2) and copy down. (header: Int)
3. Load to PQ

In PQ Editor:
Add custom columns with following formulas.

=Int64.From([Num],null,RoundingMode.Down)

=Int64.From([Num],null,RoundingMode.ToEven)

=Int64.From([Num],null,RoundingMode.TowardZero)

=Int64.From([Num],null,RoundingMode.Up)

What result do you get? Does all custom columns return same result? Or different?

 

[GraH - Guido]

Clearly different. Also the formula typed and the round down/up action via the ribbon button are returning different results.

 

[Chihiro]

Thanks, looks like Int64.From() will never return same result as INT() function.

And RoundingMode argument in this function, only impacts how x.5 is treated. Not all decimals.
Ex.
=Int64.From("1.50000001", RoundingMode.Down)
Will return 2, and same for .TowardZero etc.

=Int64.From("1.5", RoundingMode.Down)
Will return 1, but .ToEven will return 2.

On my original test, I didn't notice this as I didn't have number that fell exactly at x.5.

And yes, to simulate how Excel's INT() function works, you'd use Number.RoundDown. Not Number.Round() with RoundingMode argument.

Similar issue can be observed if you compare Excel's MOD function with Number.Mod.

To simulate Excel in PQ for MOD. You'd need something like below.

Excel: MOD(n,d) = n-d*INT(n/d)

PQ: =n-d*Number.RoundDown(n/d)