Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

Vijay_A_Verma
Most Valuable Professional
‎04-01-2022 12:05 AM

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"

 

0
Comments