Power BI - Working with dates

Hello, friends and welcome to Tuttle's Point

In this video, we are going to see how to work with dates in Power BI Desktop

In this article, we'll be focusing on the different ways of implementing and creating dates

and how to work with dates in Power BI desktop. As we all must be knowing that dates is the important part of ysis in business ytics

as it's all about the deadline when you meet any target or when you want to achieve

any target don't you think so guys right so let's take for an example that we

have a data set in which I have the sales the purchase the number of units

sold and the target to be achieved with the respect to with the respect to the

dates all right now depending on this table we need to do an ysis in such a

way that I'd like to do a forecast depending on month wise what wise air wise

and so on now there might be a scenario where I would like to get the ysis

on the previous dates or in the upcoming dates as well. We'll be having a better understanding about what are the options that we see at the time of working with dates in our practical approach

Hello friends and welcome. In this video we are going to see how to work with dates in Power BI

So let's get started off. In order to make the changes, I need to go to the Power query editor

Alright, so in order to go there, I need to click on the Edit Query button, which is under the HomeTal

the moment I click on the edit query button the power query editor window opens up

right in front of you and on the extreme left side of your screen you can see all

the queries all right now as we are talking about the date topic as we are

discussing how to make changes in dates I will go to the date query and you can

see there are so many columns now instead of making changes in the same column

what we will do out here we will create another column or we will duplicate this

query and make the changes to that particular query so that my original data does not get hamper all right so I will go under the date tab I'll simply right click on it and there's an option call as duplicate all right I simply click on it the moment I click on it another query will be created right at the bottom which is an exact replica of my date query all right this is the same number of columns which you can see on the screen as we are there for the date query all right so it's the same number of columns and

Out here what we will do we will remove the rest of the columns and we will just focus on one column and then make the changes accordingly all right so I will go under this date column I will simply right click here again and I will click on remove other

columns in such a way all the other columns will be gone I will simply click on it

the moment I click on it you can see the rest of the columns got deleted and as of

now I just have this one column which calls for dates all right so let's get

started off and see how the changes are being done Now in order to make the changes we can either go to the transform tab which you can see on the top of your screen

Or we can go under the add column tab which will next to the transform tab now the difference between the transform tab and the add column is that if we go under the transform tab and if you

Make the changes the changes will be applied only to this column itself all right now if I go on the add column the

the changes will be applied to this column and a new column will be created as the result of the changes that we have applied so that is the only difference transform will make changes in the same column whereas add column will create a new column according to our own need we will start off with add column and see what are the things which we can do in the add column section I will go to the ad column and out here you can see the date and time group all right now this is a date so I will simply click on the drop-down menu for the date

Now as you can see the rest of the option are being faded out. I cannot access any of this. There's just one option which says pass

Let's see what happens if I click on pass the moment I click on pass another column is being created

But what is the difference between these two the only difference between these two is that in our in our first column that is the date column the data types we have learned what are the data types and what is the purpose of it? Yeah in our previous video so the

date column has a data type of ABC that means it's a text but as of now as you can see

this a date section so we need to give them a understanding that it's a data type

of a date all right so in order to make the changes I can simply click on it

and convert it into a date section so that this becomes a date and we will

remove this another column and we will just work on this particular date

section now as we have converted this into a date less check whether the other option has been open to us or not so I will go under this date

drop menu now you can see all the other options are being seen right now and we can access those all right so what are the changes that we can do under the date section is very simple if I click on this drop menu you can see there are so many options your months quarter week and days

all right so what happens if I go and click on the year if I go under this year

menu and you can see there are so many options out here so if I click on here

see what happens the moment I click on near another column is being created and

this will specify that what is the year of this particular date this is the exactly same thing that we do in

Excel we use a formula which says equals to year which gives me the year of that

particular date right so this is what exactly power BI is doing for us but

it's creating a calculated column in such a way that that particular column will

show that year of that particular list entries all right so let's see next

something I will go else again go to this date column I will go under the ad column section group or the ad column tab and I'll go to

the date and time group I will click on the drop-down menu and this time will go to

the month so under month you can see there are so many options it will if I click on

this month it will give me what month that is all about if I want to know the

start of the day and if I want to know the end of them I'm sorry the start of

the month and end of the month so basically if I click on this option it will show

me what is the start of the month let us any months begins with an

one one or whatever that year is so if I click on this it will show me the start of

the month so this is the start of that month particularly and I'll again go here

go to data tab and if I go into the month end of the month will give me

31st or the 30th depending on what month it is right so similarly how many

days in the month the name of the month all the options that can be seen if I want

to check that what exactly what the month is so if I click on the name of the month

it will show you what the name of that particular date what is the month of that

particular date in our text format right I guess this very cool so that in such a

way that with a single click of a button I can get the rest of the information

about the dates let's go and explore a little more I'll go on to this date

date dropped-down menu now here you can see the quarter of the year which

whether it lies in which quarter whether it's in the first quarter second

quarter third or fourth right or whether it's in the start of the

or end of the quarter that's what you can see the very interesting thing is the week

tab and the the week tab you can see I can come to know that that particular day is under which week of the year right if it of which week of the month or but whether it

the start of the week or whether it's lying in the end of the week that's all can we figure out with the help of just one click all right guys so if I say

that let me know that which week of the year it is so if I click on this menu

it is showing me that is the first week of that particular year right and as

you can scroll down you can see this a fifth fifth six and rest of the details can be seen as you scroll down

to this particular section all right let's go and check the last option if I go

under the date tab and if I go under the days so this particular day option will

give me which date is which day of the week it is day of the year start of the

day end of the day and the name of the day so basically we do not have to

apply our efforts so that power be I is making it such a

simple task for us that with a single click I can get rest of the details quite

easily if I click on day it will show me the day what the day it is right

simply giving me the serial number according to the date that we have out here

alright it's a 19 so as you can see it's a 19 out here very simply I will

again go to the date tab and if I go under this drop-down menu it also shows me

the name of the day as you can see the last option it is showing me what

date is all about right and this can be seen under the applied step as well

what are the things that we have applied we haven't made so many changes under the date

section right it's very simple guys if you want to extract any information from

the date section you can simply go under the ad column let's go and use the

transform column this time instead of going to add column so I will select this

particular let's say this particular section which shows me the dates format I

will go under the transform column and this time I'll go into the date and time

group I'll click on this drop-down menu and you can see I can get the same number of options so if I click on here it will show me years quarters let's go for quarters I would like to know that which quarter of the year so I simply click on it it is showing me all the quarters till the I my entries are there right so we just have data for first two quarters that can be seen out here all right so it's very simple guys if you want to extract any information from the data section

I simply have to highlight that date make sure that it's in the proper format and under the transform tab or under the ad column tab you can make the changes accordingly all right guys this was all for this video see you until next time