0:00
Hello everyone and welcome back
0:03
In this video we'll see how to calculate your age in just two button clicks using Power Query
0:09
Now you see on the screen I have some names written here and the second column I have date of birth
0:14
So all the date of birth are written in such a format that this date of birth format should match with your system data format
0:21
Let me show you an example here. So you see on my screen my date of format it was it's DDMMMI
0:29
Y Y. Same data format should be given here. Okay. If the data format is something different
0:34
you have to watch the next video. So right now, for time being, if you see the data format
0:38
is same here, and we'll try to find out exact age in just two button clicks. So keep your
0:43
cursing the data. This is not a table right now. So I'll just click on data tab and I can say
0:48
as from table. So once you click on from table, it is converting into a table. I'll click on okay
0:54
Now once you click on okay the data comes in power query and you see the data type of this column is ABC You see ABC it is nothing but text data type The second column if you see that a date and time You can see very small icon which is given calendar and a
1:09
time, that's a clock icon. But what I want is, I want this data type of the column only as day
1:15
So I'll just click on the column heading. I'll just click on this calendar button and I can select
1:20
this option as date. So now it will ask me, do you want to replace the current step or add a new
1:25
step. The previous step, if you see the change type, this was automatically done by Power
1:30
Query. So it is very useful, very, you know, easy to understand. So Power Query automatically
1:35
gives the data type to the column depending upon the data inside that. So this was automatically
1:40
given. Now I'll say as replace current step and now see the data type is date column here
1:46
Now, how to find out age? So select the column, that is date of birth and click on add column
1:52
on the right hand side if you see that's a date function here I'll click on this date drop
1:57
down and there an age option there so once you click on age you get a separate column and this is your age in number of days you see that a days hours minutes and seconds So right now you see the first column first one is 23 to 773 But nobody likes the age in days
2:15
We want in total years. So let us see what is the next one. See the data type of the column
2:20
age column, it's a duration or it's a clock icon. Whenever you select this column, okay
2:26
you see on the right end side duration gets enabled and the date gets disabled. So depending upon the column which you select
2:32
the options would be enabled here. If I click on this date of birth, you see date is enabled
2:37
and that is disabled. So I'll just click on this age column because I want to find out total
2:42
age in number of years. So click on duration drop down and if you see there are so many options
2:47
By looking at the option you can understand, we'll be selecting from here as total years
2:52
So if I click on this button total years, this is your exact age in number of years. If you
2:57
see, that's the exact age. But now again it is showing me as age, my mind, my name, my
3:02
age as 24 Definitely I don like this format I want in the rounded format So I can just convert this 1 If you see the heading which is 1 it a decimal I can convert into a whole number So click on 1
3:18
And then if you see, I'll just convert into whole number. So now you can see my exact age is 22 years here, 24 years
3:25
And the other months have been remote here. So that's very simple, very easy
3:29
We had this date format. We convert into number of days. And the second click we convert into total years, right
3:36
So once you finish, you can just click on Home tab, close and load
3:41
And now a new sheet would be created. That's a sheet 2 with the edge. And the same story again, if I change anything on this particular data or if I keep on adding a new data here, on the next sheet, the data would be updated automatically
3:55
The only thing what I have to do is I have to just right click on this data and refresh
3:59
So here I can just right click and click on refresh. So it will automatically update here
4:04
right so now I hope you have understood how to calculate age in just two button clicks
4:08
using power query no formula no function very easy and automatic update that's all for this video
