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