Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.