Number.Int and Number.ExcelInt functions in Power …

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

 

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"