If you are a fan of the INT or TRUNC functions in Excel, you might be surprised to learn that there is no built-in equivalent in Power Query's M function language. Today's episode shows a simple formula that you can use in a Power Query custom column to replicate TRUNC and INT.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2394.
How to TRUNC in Power Query (It's better than INT).
Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
I learned something was I was doing this video.
I use the INT() function hundreds of times a year.
It is my go-to function.
I just need to chop the decimals off.
Super easy to use.
Equal I-N-T of B5.
I just did a test here and I was really surprised.
If I take the INT of minus 3.412.
I think I am going to get minus three.
I don't.
I get minus four.
How weird is that?
It's going to the integer just smaller than what we have.
Super super annoying.
So I learned that what I need to use is not the INT function, it's the TRUNC function.
The TRUNC function.
So take the TRUNC of B5 and say that I want to truncate it to 0 digits.
And when I copy that down it will do what I want it to do.
What I thought INT would do for the -3.42 it just cuts it down to minus three. Now, that puts me in a bad mood because TRUNC is 2 characters longer than INT and it requires a comma zero.
That means that I'm typing four extra characters every single time, which makes me say ARGH.
OK, but I wanted to do a video about how to do INT in Power Query, but I'm not.
I am going to do a video about how to do TRUNC in Power Query because I think that's really closer to what I wanted it to do.
Alright, so we have a number here.
1.234567 and various number of digits.
How many digits I want to truncate it too.
So if I truncated to 1 digit I get 1.2.
If I truncated to 5 digits, 1.23456 – it cuts the seven off.
For negative numbers.
Same thing.
It's not shifting to the next lower negative number or anything like that.
Alright, so let's do Data, From table or range.
And we want to create a calculation here in Power Query that matches the Excel TRUNC function.
So I'll Add a Column, Custom Column.
I'll call it, I guess, PQ TRUNC for Power Query version of TRUNC.
We are going to do equal number dot round toward zero. and I pass the number.
If you just always wanted to do what I thought INT did, if you just take it to the integer, you put a zero there.
But here since I have the digits, I can put the digits in.
Close paren.
Take a look at that OK, right?
So Excel gives us 1.2.
One point two.
One point two three four.
The negative numbers?
They are all working.
Simple, so number dot round toward zero.
Number and the number of digits is equivalent to Excel's TRUNC function Which for positive numbers is equivalent to Excel’s INT function.
Well hey check out my new book MrExcel 2021, Unmasking Excel.
Click that I in the top right-hand corner For more information about that.
If you like these videos, please Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Thank you stopping by.
We’ll see you next time for another netcast from MrExcel.
How to TRUNC in Power Query (It's better than INT).
Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
I learned something was I was doing this video.
I use the INT() function hundreds of times a year.
It is my go-to function.
I just need to chop the decimals off.
Super easy to use.
Equal I-N-T of B5.
I just did a test here and I was really surprised.
If I take the INT of minus 3.412.
I think I am going to get minus three.
I don't.
I get minus four.
How weird is that?
It's going to the integer just smaller than what we have.
Super super annoying.
So I learned that what I need to use is not the INT function, it's the TRUNC function.
The TRUNC function.
So take the TRUNC of B5 and say that I want to truncate it to 0 digits.
And when I copy that down it will do what I want it to do.
What I thought INT would do for the -3.42 it just cuts it down to minus three. Now, that puts me in a bad mood because TRUNC is 2 characters longer than INT and it requires a comma zero.
That means that I'm typing four extra characters every single time, which makes me say ARGH.
OK, but I wanted to do a video about how to do INT in Power Query, but I'm not.
I am going to do a video about how to do TRUNC in Power Query because I think that's really closer to what I wanted it to do.
Alright, so we have a number here.
1.234567 and various number of digits.
How many digits I want to truncate it too.
So if I truncated to 1 digit I get 1.2.
If I truncated to 5 digits, 1.23456 – it cuts the seven off.
For negative numbers.
Same thing.
It's not shifting to the next lower negative number or anything like that.
Alright, so let's do Data, From table or range.
And we want to create a calculation here in Power Query that matches the Excel TRUNC function.
So I'll Add a Column, Custom Column.
I'll call it, I guess, PQ TRUNC for Power Query version of TRUNC.
We are going to do equal number dot round toward zero. and I pass the number.
If you just always wanted to do what I thought INT did, if you just take it to the integer, you put a zero there.
But here since I have the digits, I can put the digits in.
Close paren.
Take a look at that OK, right?
So Excel gives us 1.2.
One point two.
One point two three four.
The negative numbers?
They are all working.
Simple, so number dot round toward zero.
Number and the number of digits is equivalent to Excel's TRUNC function Which for positive numbers is equivalent to Excel’s INT function.
Well hey check out my new book MrExcel 2021, Unmasking Excel.
Click that I in the top right-hand corner For more information about that.
If you like these videos, please Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Thank you stopping by.
We’ll see you next time for another netcast from MrExcel.
