Creating Dynamic Calendars - Application

Quick reference

• 00:04 So we need a calendar table on our model,
• 00:06 the first thing we need to do is figure out where we're gonna get the information.
• 00:11 In this particular setup,
• 00:13 the most logical place is our budget's table, because, just above it,
• 00:17 we actually have a couple of Excel cells that are driving the entire thing.
• 00:22 You'll notice it says for the two years beginning, and this is an input for
• 00:26 January 1, 2009, and the ending uses the EOMONTH function to actually pull out
• 00:32 23 months later, or 24 if you like, giving us December 31st, 2010.
• 00:37 This spans the entire range that we actually could have
• 00:40 transactions in this model, so it's a perfect one to start with.
• 00:44 So here's what we're gonna do, I'm gonna go and select this cell, and
• 00:46 I'm gonna create a named range for it.
• 00:49 I'm gonna click in the name box, and I'm gonna type in StartDate.
• 00:53 I'm also gonna go over and find this other guy over here, that's my end date, and
• 00:58 I'm gonna go and name this one EndDate.
• 01:01 And this is the faster way of setting up a name range than going into
• 01:05 the Name Manager.
• 01:06 But you'll see, if I go in here, that here's my StartDate, here's my EndDate and
• 01:10 of course the RangeBudget and the Departments tables and
• 01:13 whatnot that we've already created.
• 01:17 Now, because this is a name range, I can select it and
• 01:21 say StartDate, and then I can go and
• 01:24 create a new query to grab my data from other sources from table arrange.
• 01:30 This will create me a new query and
• 01:32 I need to do something very specific to the start date query.
• 01:36 I need to convert it to a date, not a date time, I'm gonna replace that step,
• 01:43 and then I'm going to right click on this cell and choose Drill Down.
• 01:50 And that will take me into just this data point.
• 01:54 I'm now gonna say, Home > Close and Load To >
• 01:59 it to only create connection and not land us in the data model.
• 02:04 So just create the connection, and you can see, here it is.
• 02:09 Let's do the same thing for the EndDate.
• 02:11 Get data from other sources, from table arrange.
• 02:18 We're gonna go change it to be just a date > replace
• 02:23 current > right click > Drill Down, and there it is,
• 02:28 there's my EndDate that I can now say Home, Close & Load To.
• 02:34 At this point, we're gonna load this to Only Create Connection as well and
• 02:37 not add to the Power Pivot data model, and we can say OK.
• 02:42 Now I want to create my calendar, so I'm gonna say, Get Data, or
• 02:47 new query from Other Sources, from Blank Query.
• 02:52 This will launch me into the Power Query editor in a completely blank query and
• 02:57 I need to play around now with the formula bar.
• 02:59 If you don't see it, go to view tab, and check the box for formula bar, but
• 03:03 it should hopefully be there for you.
• 03:05 Also, while I'm here, I'm also going to expand the queries pane and
• 03:10 I'm just gonna move my StartDate and my EndDate into the stage in queries.
• 03:15 This guy, I know,
• 03:16 is eventually gonna be called calendar so I'll just take care of that right now.
• 03:22 I'll move it into my data model group.
• 03:24 And here's what we're gonna do, we're gonna type in this very specific formula.
• 03:27 It's gonna be equals, curly bracket, number dot from,
• 03:32 it's gonna be StartDate, notice there's no spaces in this.
• 03:38 Dot, dot, number dot from, EndDate,
• 03:41 close the parenthesis, close the curly bracket and hit enter.
• 03:47 And this will give me my list of numbers that I'd mentioned before.
• 03:50 Now a list is something very specific in Power Query, you can't do a lot with it,
• 03:54 you'll notice that all the transforms and add column stuff is all grayed out.
• 03:58 But on the list tools transform, you have the ability to convert it to a table.
• 04:03 So you do that, and it's gonna pop-up a dialog, and you can just click OK as fast
• 04:07 as you possibly can on this dialog, cuz you never make any changes here.
• 04:11 That turns this into a table, and now you can see that the transforms and
• 04:15 add columns are all useful again.
• 04:17 First thing we're gonna do, we change the data type to a date,
• 04:22 there are our dates, I'll double click on a column header and rename it to Date.
• 04:28 I've got a counter table, it's just that simple.
• 04:31 I'm gonna add a couple more things to it though.
• 04:33 Let's go add cColumn > Date > Year > and Add a Year.
• 04:40 Now I need to select the Date column again in order to light up this command.
• 04:44 If I'm still on Year, notice that Date's not lit up so
• 04:47 we go back to Date and we'll go and we'll say, let's give me a Month.
• 04:52 That gives me the Month number, go back, Date again,
• 04:55 Date > Month > we'll choose Name of Month.
• 05:00 Now, I'm not a big fan of really long month names, so
• 05:03 I'm gonna grab him, Month Name.
• 05:06 I'm gonna go to Transform, and
• 05:10 in the middle here, there's this thing called Extract.
• 05:13 And from here, I can extract the first
• 05:16 three characters of my month name to make nice short month names.
• 05:20 So, again, there's all kinds of things you can do with Power Query,
• 05:23 I'd really encourage you to explore these tabs around.
• 05:26 But for right now, this guy is done, I'm in a good stay here.
• 05:29 So what I'm gonna do is gonna say Home > Close and Load > Close and
• 05:33 Load two, And I'm just gonna show
• 05:38 you what happens if you accidentally say Table, it will create a table for you.
• 05:43 The fastest way to take care of this and get it back to a connection only state
• 05:47 is actually to go right click > delete the worksheet > and say yes delete.
• 05:53 Notice that the calendar query is still here but
• 05:56 now it's marked as connection only.
• 05:58 I can right click, and I can say load two in this case,
• 06:03 to change it to add it to the data model, and we'll say okay.
• 06:07 This will now load into the data model, it's creating relationships.
• 06:12 I'm now gonna go back to Power Pivot > and
• 06:15 manage > take a look at my diagram view > grab my date
• 06:21 > link it to the transactions table > grab my date > link it to the budgets table.
• 06:27 And then, of course, this is the many side of the relationship, right click and hide.
• 06:33 This is the many side, right click and hide.
• 06:37 And now, I've got a beautiful calendar that's linked into my model.
• 06:41 So, when I go back to my summary page, if I actually want to see my sales by year,
• 06:47 I can go and say Calendar > and Year, drop this above values and
• 06:51 it will actually segregate and everything will slice quite nicely.
• 06:55 So, I've now managed to link one more bridge table into the model, and
• 06:58 everything's working beautifully.

Lesson notes are only available for subscribers.