In this article, we’ll show you three ways to truncate numbers in Power Query so your numbers become easier to work with. It’s different from rounding because it doesn’t round values to the closest integer. Instead it simply chops off the decimal. Let’s dive right in.
What does it mean to Truncate a Number
Truncation means to chop off the decimal portion of a number. So for example, if you truncate the number 3.333, you get 3. With truncating you don’t take into account what the closest number is. That means that when you have a number like 3.99 a truncate returns 3. In that way it is different from rounding.
There are a few reasons you might want to truncate numbers in Power Query.
- A common use case is when you’re working with large numbers that you don’t need to be super precise about. For example, you might be working with population data and only need to know the number of people in a certain area to the nearest thousand. In that case, truncating the number to the nearest thousand would give you a more manageable number that’s easier to work with.
- In another scenario you may be working with financials. And you may only need precision on dollar level. This can help you get a better sense of the overall financial picture without getting bogged down in the details.
How to Truncate in Power Query
So how do you truncate numbers in Power Query? The easiest way would be to use the “Truncate” function. Unfortunately it does not exist, but we can use the following workarounds.
First, you can use the function Number.IntegerDivide. If you only want to return whole numbers, you can use:
= Number.IntegerDivide( [Value], 1)
Next, you could think about rounding the Number. The first thing coming to mind may be using the Number.Round function and round to 0 decimals. However, we don’t want the value before the decimal to change when the decimals are 0.5 or higher.
Instead you can use the Number.RoundTowardZero function. This works for both positive and negative numbers.
= Number.RoundTowardZero( [Value] , 0 )
Last, you can also extract all characters before the comma delimiter. To do that you first need to turn the number into a text value, you then extract all text before the comma and transform the text value back to a number value.
= Int64.From( Text.BeforeDelimiter( Text.From( [Value] ), "," ) )
In case you have multiple decimals you want to return the easiest way is to use the Number.RoundTowardZero function. That means if you input 4286,0679 it returns 4286,06 with below code:
= Number.RoundTowardZero( [Value], 2 )
So there you have it! Three different methods for truncating numbers in Power Query. Each method has its own approach. Familiarize yourself with all of them so you can choose the right one for your specific situation.
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Create Calendar with Dynamic Language