cancel
Showing results for 
Search instead for 
Did you mean: 
Vijay_A_Verma

Number.Int and Number.ExcelInt functions in Power Query M Language

Use Case: There are many scenarios where we are required to extract the integer portion of a number.

Hence, for 23.57 and for -45.678, the integer portions will be 23 and -45 respectively. 

Power Query Number functions library doesn't provide any function which can do this. Hence, we will work out a formula to make a Number.Int function.
Now, INT function in Excel has a little twist. If you apply INT function to 23.57, the answer is 23 wheras answer for -45.678 is -46. Hence, in the case of negative numbers, it is rounding away from zero. Once again, Power Query Number functions library doesn't provide any function which can provide Excel's INT functionality. Hence, we will work out a formula to make a Number.ExcelInt function.

Below are some set of numbers and expected answers against Number.Int and Number.ExcelInt functions

INT.png

 

Solution: 

1. For Number.Int, below formula can be used

= Number.IntegerDivide([Data],1)

2. For Number.ExcelInt, below formula can be used

= if Number.Sign([Data])>=0 then Number.IntegerDivide([Data],1) else Number.RoundAwayFromZero([Data],0)

To test the formulas above, Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAz1zM1s1CK1YlW0kXmGOgZm0BEESxDPQMDQzATwTIAsqBMXSS2gZ4pTAzIiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Number.ExcelInt", each if Number.Sign([Data])>=0 then Number.IntegerDivide([Data],1) else Number.RoundAwayFromZero([Data],0)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Number.Int", each Number.IntegerDivide([Data],1))
in
    #"Added Custom1"

 

Comments

For the Number.ExcelInt function why don't you just use Number.RoundDown([Data],0)?

Polls
What is your favorite Power BI feature release for September 2022?