In this article, we’ll show you 3 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.
Why Truncating
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 the dollar level. This can help you better understand 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 the below code:
= Number.RoundTowardZero( [Value], 2 )
Conclusion
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. If the rounding causes an issue, make sure to familiarize yourself with how Power Query works with precision. It can cause rounding differences that you may not expect.
Happy Querying!
Recommended Reading…
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
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate