Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us at FabCon Vienna from September 15-18, 2025, for the ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM. Get registered
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.