DATE TABLE for Power BI using Power Query

in almost every power bi report that i create i have a custom date table and to create these tables you can follow similar steps every single time now let me show you why you also want to work with a custom data table and how you can create one using power cream [Applause] welcome to how to power bi my name is boss and if this is the first time for you visiting this channel then make sure to hit that subscribe button if you want to stay up to date on everything power bi related we are posting short videos info videos and practice rounds every single week let's first start off by having a look at why do we need a custom date table in the first place now here i have a data set very simple where you have the quantity sales amount and have three columns one for order date one for due date and one for shipping date okay now i'm going to switch to the report view i'm going to build a simple bar chart all right so i'm going to add the sales amount and i'm going to let's say break it down by the order date now you see i actually in my database i have just one column for the order date however there is this little date i can right in front of it and when i click on it then what happens is it gives me the whole hierarchy it automatically gets generated for you and allows you to basically go from the year and then expand down one level to the quarter to the month to the day you might think oh that's super convenient i have over here the year quarter one day for at the due date or the date and shipping date however there's also a big drawback and that is that power bi creates for each datecon that you have a date table in the background okay now you might be thinking i don't see that data table let me show you how you can actually see that there is a hidden date table now if you go to to like deck studio and you connect to a power bi file then you will see that actually we have some hidden tables there we do not only have fcd sales which was the only table that i could see in my data model however we have there also local date table with the date the hierarchy day month month number quarter quarter number yeah and the same thing again for the next one and the third one over there so that is drawback number one but there's a second one and that is that these hidden date tables that get generated for you well you cannot change them so that means when i for example create a breakdown by months then i have the full names of the months when i want to have the abbreviated form well i cannot change that okay because that table is hidden and power bi doesn't allow me to change it now in a similar way what if i want to create a breakdown by week well in these generated data tables there's no weak column and i cannot add it to it okay so there's not much flexibility now because these generated tables do not offer much flexibility and are also not very efficient it is usually better to build your custom data tables now to build a custom day table we have two options we can either use dax or power query now for this video we're going to have a look how we can do it with power query so follow me to transform data and let's go over all of the steps to build a date table from scratch however in the end you can just keep on reusing the code that we're going to right now together now let's first start by going here to new source and insert a new blank query a date table starts with one column the date column that has all of the dates of the years of the period that you're analyzing okay so if we have data going from let's say 2020 until 2021 we go from the first of january 2020 all the way up to the 31st of december 21 okay now how can you create a column like this now first of all we need a starting date and an ending date and that's what i'm gonna write down in the formula bar now if you don't see that formula bar then make sure to go to view first and turn the formula bar on all right and then we can type in the formula bar the starting date let's say we're going to start on the 1st of january 2020 and we're going to have that an ending date as well so over here i'm just going to type in the ending date so that's going to be the 31st of december 20 21. so now we can rename these steps now you can do that over here on the right hand side and the applied steps so custom one that is my ending date so i'm just going to write here and date so that's basically one variable then over here the source i also want to rename but it's grayed out now the source tab you can only rename if you go to view click on advanced editor and then from here i can rename source to something else so i can name this start date okay now over here what gets returned will be the ending date okay now we can also put a comment or more information so what we are defining now are the variables that we're going to use in the functions later on so to put in comments i can just do forward slash forward slash and then type in something like okay here are my first variables i'm going to click on done and you see here on the applied steps we now have a starting date ending date and there's this info button and when you hover over it it basically shows what was in that comment so now that we have starting date handing it let's build this list with all of the dates in between so here i'm going to use a function that's called list of dates and this function needs first of all starting it which we already have in a variable so i can just refer to the start date variable then we need to say the count okay now let's say that we want to have the dates for one year okay so then i could just type in 365. when it's here with 365 days and then the step duration well here i can say that it should be a day step okay so if you want to type in days in power query you can just do a hashtag and then type in duration and then in between brackets you can say one day zero hours zero minutes zero seconds you see that gives me all of the dates from the first of january 2020 all the way up till the end of 2020 however one day is missing the 31st of december because actually i needed to have 366 days for 2020. now of course i could just dive in 366 but it's not a very ideal solution so instead of that we are going to have a variable that calculates the correct duration for us okay so i'm going to go one step back over here after the ending date i'm going to have a new step and here i would like to calculate the duration now there are functions related to durations and we can say take the duration from and here i can calculate the difference between the ending date starting date so i go for end date minus the starting date and you see that gives me 730 and then zero zero column zero zero is equal to zero so there's one more thing that we need to do it's almost correct however we need to extract the number of days from here okay so we need to say duration again and here i want to extract the days from the duration that was returned so i'm going to use duration dot days and then put what i already wrote in between close an extra bracket over there at the end and now it returns 730 so let's rename that step as well to duration then and now i can go over here to custom one let's also rename that one to dates and then instead of writing there 365 i'm just gonna write now here duration now let's see if that solved the problem so i'm starting on the first of january 2020 go all the way till the end you see i'm still missing one day and the only adjustment that i need to make is that i go back to that duration step and then add one okay so i'm going to say plus one then i can go back to dates and the problem is solved now at this point we just have a simple list with dates now if i want to add more columns to it well i cannot leave it as a list i need to first convert it to a table that's very easy you can just go here to the top transform and then click on to table and just click on ok so now it's a table and i can do all of the standard power query transformations so let's start by renaming that column to date and then also apply the correct date data type so date well this was basically the difficult part so the only thing that's left to do is adding some columns to it like the year the quarter the month okay and that you can all do using the functionalities in the add column and then date where we can say for example year and i want to have a new column that shows the year based on that date column okay so now that we have the year we can select the date column again go to add column date and now i want to have a new column that shows the quarter now you see that for the quarter it returns one two three and four okay so this is going to be the quarter number column now we don't need to create an additional step rename it what we can do instead is just go over here to the formula bar and rename it directly inside of the formula bar that saves us one additional step so now that we have the quarter numbers we want to have another column that shows q1 q2 q3 q4 to create that column we can go over here to add column now we're going to go for custom column and we're going to call this one a quarter where we want to have q and then we want to combine that with the quadrant number all right double check no syntax errors that looks good i'm going to click ok and oh disappointment errors let's have a look what the arrow is so over here the expression we cannot apply operator and to types and text and number now to solve this problem we can go here to the formula bar and you see we're combining something that's text with something that's a number all right so to solve this problem we just have to say text dot from and then just wrap that quarter number inside of that function and you see the problem is solved the red bar stays however if you go back and then four again and taps then it becomes green again okay so now we have the quarters then we also want to have the months of course so i'm going to go back to date column add column date and then we can extract the month that gives me the month number and also here i would then rename it to month number and then we go back to date column add column date month and then we go for the name of the month now here for the name of the month you see it gives you the full name of the month maybe you just want to have the abbreviation or the first three letters then you can go to the formula and say text dot start and then over here this is the text and i want to extract the first three characters and now i just have the first three characters of the month you can also add the columns for the weeks and for the days as it is exactly the same let's skip forward now once you have all of the columns that you need and this can also include the fiscal year quarter month column then clean up the applied steps and you can do this by first of all making sure that everything is named properly once you have done that you can also open the advanced editor and then include any comments that might help you or your colleagues in the future so here i could for example write date columns as so everything that starts here from that date step all right so now i basically have the whole date table now two things that might be still quite helpful and that is instead of just writing the start and the ending date here as variables in your query you can of course also use parameters so i can also go here to the home tab and then click on manage parameters and then choose new parameter and then here as the parameter name i'm going to say this is going to be my start date okay it's required and here it needs to be a date now suggested values can be anything or a list now let's go for current value of let's say zero one slash zero one slash 2019 so here we can now change where the date table should start however the only thing that we still need to do is go back to a query and then choose the starting date so instead of hard coding the date in there i can also use the equal sign and then just refer to the name of our parameter now of course i can do the same thing for the ending date create a parameter and make that more flexible as well now another common requirement that i have is that the ending date actually should be determined by the date of today so if we have today some data in let's say 21 that i go and take the very last date of the year that i'm currently in okay so to do that we have to go back to the applied steps and i'm going to go over here select ending date and then insert a new step now to return the date of today in power creamy what you do is you use a function that's called a datetime dot date and then we can use again date dot and then local and then over here we have daytime local now okay i forgot over here the open and closing brackets for local now then the closing bracket for the datetime date function and that gives me the date of today so we now want to have the last date of that year okay so i'm going to use another function which is going to be date dot end of year this one over here and that turns it into the very last date of the year so also here i would rename that step then to today and now you can just go back to your ending date and then instead of having here equals ending date we're going to refer to today which is then being picked up by the duration step and everything automatically updates when we would enter in a new year all right so we are ready to load our date table to the data model just make sure that you also give it a nice name go for dim date or calendar whatever you like then here on the home click on close and apply so once you have loaded your data table to the data model we have to connect it to the main fact table or fact tables so in this case we could for example take here the damn date and then date column and then just drag and drop it onto let's say we want to have the connection on the order date okay so the connection is there then we need to make some adjustments to the date table itself so let's go here to dim where you first of all want to select all of the fields and then you go here to advanced and then here on the summarize by you can say none so that makes sure that when you put in the year field or the quarter field that it doesn't start summing it up because it doesn't make sense all right so once you have done that you go to the quarter and the month and the day fields to make sure that they are sorted in the right way all right so i can go here first of all to quarter well the quarter needs to be sorted by the quarter number okay so also here under advanced we can choose the sort by column which is the quarter number and then we're going to do the same for the month because we don't want the months to be sorted in alphabetical order so therefore i'm going to select the month advanced and then choose here the sort by column month number and then for the day same thing also here we need to select it go to advanced and choose the sort by column now once you have that then you can go to the report view create a hierarchy so i can just drag quarter onto here and then you add the month and the day as well and now you can rename that from year high key to date ikey or calendar or whatever you like so these other columns we don't need to show anymore so i'm going to my data modeling view again and then i'm going to hold my shift key select all of the columns that i want to hide and make sure to hide those columns all right so now that we have that and we go back you see it only shows dialing key now one important thing that is left to do and that is we actually didn't say yet that this is the custom data table now to do this we have to select the date table go to table tools and then click on mark as date table and then as a date column we have the date validated successfully that's all good now we are almost there because you can see that actually these hidden date tables that were so inefficient that we talked about at the beginning they're still there as well all right so to get rid of those we have to go to file and then let's go here to options and settings and then options and then go to current file data load and turn off outer date and time all right now if you want to have that for every single file in the future and then you can just go over here to global data load and turn off out the date time for new files and click ok i see that the little calendar icon in front of due date or the date shipping date disappeared because now we have our dim date table that we're going to use for visualizations so the only thing that's left to do is create your visualizations using your newly created custom date table so here if i want to break down my sales buy the different dates then i can just put on that date high key or calendar or however you named it onto the access and now it gives me the years and i can expand down through the quarter and to the month level there you go and if you need more columns then you can just keep on adding them to your date table for whatever your needs are now of course i wouldn't do this totally from scratch for every single power bi report that i would create instead of that i just copy over the code okay so i would go open power query and then follow the same steps as what we did today however instead of doing it step by step i would just go straight away to view advanced editor paste in my code click on done then rename my query and then go to the home tab and click on close and apply and that's it i hope this video was helpful if you still have any questions just let me know in the comment section below and if you liked it then consider subscribing and i hope to see you in the next video

Channel: How to Power BI

Views: 117,601

Keywords: 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, power bi desktop, power bi training

Id: MhC4zj2byBQ

Length: 21min 26sec (1286 seconds)

Published: Mon Jan 25 2021