Dynamic Date Table Using the Power Query Editor

hello everyone my name is Angelica Dominic and I'm a trainer here at pragmatic works if this is your first time visiting our YouTube channel make sure to hit that like And subscribe button below to stay up to date with all of the videos that we post today I would like to talk to you about creating a dynamic date table using the power query editor so recently I was working uh oneon-one with someone and they had a particular request for a date table and they needed some help making this date table Dynamic and they wanted to you know be able to have a date table to analyze their report their sales being able to look in the past a few years prior as well as being able to look at some dates ahead in the future to be able to do some forecasting and things like that so we went in and set up that date table for them using the power cre editor so I thought why not go ahead and create a video on that here now so the first question is why do you need a date table so if you are working with your your model and you already have a date column and you can use that date column in the report to be able to analyze the data that you are looking at what is the need for a separate date table well one reason why you might want to add a separate date table is to be able to carry out time intelligence functions powerbi has a set of time intelligence functions that you can utilize but it does require a date table so without a date table you can't easily perform those time intelligence functions like year to date quarter to date month to date so that's one reason another reason is it allows you to be able to you have more control over the granularity of your data you can drill down into specific levels like the year the quarter the month the week and even the day to provide the additional de levels of detail in analyzing these dates you can customize the date table to include additional columns like fiscal year fiscal period holidays working days and another reason is you might have more than one fact table in your model and if you have more than one fact table creating that separate date table as opposed to just leveraging a column on one of those fact tables allows you then to build relationships between each fact table this will then allow you to filter the data on both fact tables you can use that date table and you can then filter you know the data on this fact table looking into a particular quarter or looking into a particular year using that same date table so let's go ahead enough talking let's go ahead and dive into the powerbi desktop and the power query Editor to take a look so here I have uh just a sample report uh using uh failed Banks data set that I got from data.gov so you can see here I have a bank name column A City a state a closing date and a citystate column now we do have that closing date column but again remember I want to create a full date table that I can use here and that is going to allow me to slice by some additional fields that we're going to add in here and we're also going to make this date table a bit Dynamic so I'm going to go off screen here so I won't be in the way of anything at all now the first thing that you're going to want to do in creating this new date table is to go in here and create a new blank query so from the Home tab in the power query editor go ahead select new source and blank query now up here in the formula bar what we want to do is we want to add in the following we want to add in this function here date time local now and once we add in date time local now we're going to add an open opening and closing parentheses and what this function does is this Returns the current date and time in the local time zone when we are invoking it here in power query now so you can see how we currently have this and we want to now select convert to table so select convert to table here and you'll now see a new column created with just that date and time we'll go ahead and change this here to a date so that we are just looking at the current date here the next thing that we would want to do and we can go ahead and uh rename this here from column one to current date to make it a bit easier here for us to understand now the next thing that we want to do uh let's imagine that we want to take a look at the dates for the past 3 years we don't really need to look any further back than that for this specific report that we're building so we want to look back into the previous three years what we can do here now is we can go ahead and and select add column and go to custom column now we're going to create a custom column and we'll call this the 3 years prior is what we'll name this column here now for this column what we want to do is we want to utilize a function known as date. ADD years now when we use date. add years we're going to reference that current date column that we created and so as I type this out here and I type a square bracket you can see it's going to reference The Columns that we have existing here now date. add years is going to add a specific number of years from the given date that we're referencing in this expression now we're going to go in here and add a number of years after a comma and for our example in this column we want to look back three years so we are going to put minus 3 then we're going to go ahead and click okay now we're going to go ahead and change the data type of this 3 years prior column that we have created to a date now that we have done that here the next thing that we want to do is we want to take a look at adding in the start of the year so although we want to analyze the data that we have in this failed Banks query back three years prior I really do want it to go back to the start of the year that way as I go in and I analyze data by the quarter by the month I'm not excluding any the months from that year so we'll go ahead select add column custom column here again and now for this column we'll call this our start of year or actually let's go ahead we'll call this our start date and I'll show you why here in a second because this is what we're going to leverage to be the start date in that full date column we're creating so for this function here for this column we are going to use date. start of year and so that's the one we're going to use here and so I'm going to scroll down here and pull that in oh let me go ahead and remove that extra date that got added in there now what we want to do is we want to reference that 3 years prior column because we want to see the start date the very first date of the very first year that's what date start of year is going to return to us and it's going to return to us the very first date of the Year 3 years prior so what date do start of year is going to do is it's going to return to us the first date from this three years prior column so I'm going to add a closing parentheses now in here and then we'll hit okay and we will see January 1st of the year 2021 now we need to change the data type here to a date as well and we're going to go in and add one more column here now and then we're going to create our full day column so I'm going to select add column and custom column here now and the next column that we are going to create is going to be the end date and so this is what we're going to use here to be the final date for the full date column we're going to create now for this particular one we're going to kind of model it after that scenario I mentioned looking 6 months into the future and so we want to create a column that's going to allow us to look at this into the future so I'm going to say date. add months here now and what I want to see is I want to refer six months into the future so date. add months and I want to refer to that current date column because I want it to look six months into the Future Past today's current date so I'm going to add a comma here now add in the number six add a closing parenthesis and then hit okay now I'm going to change that end date column to a date data type as well now we're ready here now to get into the final column we are going to create and what we're going to do in this next column is we are going to create a column that's actually going to be a list so let's go ahead and create that list so go up to the add column tab select custom column and this column here is going to serve as our full date column now this column is going to be a list that we create and the list is going to include all dates between our start date and our end date column Now to create a list we must create this list inside of curly brackets and so as we create this here now what we are going to start typing is the following we are going to use a function known as number. from to create this and then we are going to refer to that start date column so I'm going to bring in that start date column here now then we're going to add a closing parenthesis and then to create a list we're going to type out two periods here now now the second part of this will be to use that number. from function again and we are now going to bring in the end date so I'm going to bring in that end date column We'll add a closing parentheses and a closing curly bracket and then we'll go ahead and hit okay now what we must do is expand out this list that we have created to new rows so go ahead and hit those arrows to expand to new rows and now you're are going to see that full date column once you change that full date column to a date data type you will see your full list of dates now you can go in and you can remove these other columns because as you can see these are columns that we will no longer need so you can go to the Home tab select choose columns uncheck select all and just keep the date column there now you can easily go in here and add in other date columns by going up to the add column Tab and then in this date section here you can go in and select year and it's going to parse out the year for you from that full date column we can continue on here selecting that date column the date button again and now we can give a quarter column and select and add that in here select that date column and now we can also go in here and add in a month column to get the month number Select the date column again and now we can go in and add the name of the month to get the month name column and you can continue on from here and you can continue adding in new columns using that date list column we created we would then go over here to this query and call this our date table here now and then we can keep going in and adding to our model I hope you enjoyed this video and seeing one way to go about creating a dynamic date table in the power cre editor that you can use to carry out time intelligence functions to look at the data and your reports at different levels of granularity using those dates slice fields to analyze by the Year by the quarter by the month if you haven't already make sure to hit that like And subscribe button below to stay up to dat with all of the videos that we post

Channel: Pragmatic Works

Views: 9,419

Keywords: Power BI, date table, date table power bi, power bi date table, power bi date hirarchy, power query date table, date table power query, date table m, date table m power bi, date table template, date table in power bi, date table in power query, create date table, creating date table, simple date table, power bi date table template, power bi desktop, power bi training, dax power bi, power bi time intelligence, time intelligence, data analysis, dynamic startdate and enddate

Id: lfFRiHrPWOI

Length: 12min 14sec (734 seconds)

Published: Mon Mar 18 2024