Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,450 --> 00:00:05,890
I want to share protip with you guys that I think is pretty cool and it's creating a rolling calendar.
2
00:00:06,000 --> 00:00:12,270
So in the case that we just walked through we loaded up a discrete set of dates because we only wanted
3
00:00:12,270 --> 00:00:16,490
to analyze the data that we had on hand in our particular case.
4
00:00:16,620 --> 00:00:19,200
We won't need to change that calendar.
5
00:00:19,200 --> 00:00:23,370
We won't need to pull in new data or analyze new information over time.
6
00:00:23,400 --> 00:00:29,130
But what if you're in a situation where you do you know maybe you've built a weekly report that you
7
00:00:29,130 --> 00:00:33,750
want to refresh with new data every week and share with your colleagues or your boss or your client
8
00:00:34,230 --> 00:00:38,880
rather than creating a fixed calendar and updating it over time.
9
00:00:38,880 --> 00:00:44,550
What we're gonna do is actually use a little bit of code and I know this is the last bit EVENT THAT
10
00:00:44,550 --> 00:00:45,430
WE'RE GONNA TOUCH.
11
00:00:45,510 --> 00:00:51,330
But trust me it's worth it when you use that code to create a calendar that will update based on the
12
00:00:51,330 --> 00:00:56,840
current point in time so it will act as a rolling calendar that evolves as time goes on.
13
00:00:56,850 --> 00:01:01,230
So I'm going to lay out the steps here and then we're gonna jump in a power by and I'll demo what that
14
00:01:01,230 --> 00:01:02,460
looks like.
15
00:01:02,460 --> 00:01:08,130
So step one this is gonna be a great time to test that blank query option that I showed you earlier.
16
00:01:08,340 --> 00:01:12,990
So create a new blank query and then enter formula bar.
17
00:01:12,990 --> 00:01:18,030
We're gonna generate a starting date by entering something called a literal and you're gonna write it
18
00:01:18,060 --> 00:01:25,230
exactly like it's shown here with a hash mark the word date and then a starting date whatever you choose
19
00:01:25,350 --> 00:01:28,590
in the year month date format.
20
00:01:28,590 --> 00:01:34,740
So in the example we're looking at here on the slide this would represent January 1st 2018.
21
00:01:34,740 --> 00:01:40,440
From there you're going to click the effects icon which adds a new custom step and you're gonna enter
22
00:01:40,440 --> 00:01:42,600
the following M formula.
23
00:01:42,630 --> 00:01:48,810
Now I know it's a lot and I'll post this function into the Q and A section as text so you copy and paste
24
00:01:48,810 --> 00:01:53,490
it but we're not really going to dig in and unpack how this formula is working we're just gonna use
25
00:01:53,490 --> 00:01:56,670
it almost like a recipe to create our list of dates.
26
00:01:57,360 --> 00:02:03,510
So once you've added that formula a list will populate in the query editor and then we're gonna convert
27
00:02:03,510 --> 00:02:07,610
that list into a table and then we'll format that column as a date.
28
00:02:07,830 --> 00:02:13,290
Last but not least if you want to you can add some more calculated date columns just like we showed
29
00:02:13,290 --> 00:02:14,650
with our last example.
30
00:02:14,780 --> 00:02:18,690
You can add years months week whatever you need and using those add column options.
31
00:02:18,780 --> 00:02:21,070
But it's really as simple as that.
32
00:02:21,090 --> 00:02:27,450
So let's hop in to power b I am going to write out this formula and I'll show you exactly how it works.
33
00:02:29,110 --> 00:02:34,140
All right so I'm back in my adventure Works report this time I'm gonna go into the get data option but
34
00:02:34,150 --> 00:02:39,130
instead of choosing CSB I'm going to pick that blank query option down the bottom.
35
00:02:39,130 --> 00:02:45,190
This takes me straight into the query editor and creates a new query just called query 1 and if we wanted
36
00:02:45,190 --> 00:02:49,980
to we could give this a name like rolling underscore a calendar.
37
00:02:50,080 --> 00:02:55,660
Don't worry too much about this because I will be deleting this query after I write it but just for
38
00:02:55,660 --> 00:03:01,840
the sake of habit you'd want to give that a proper table name and then inside of the formula bar.
39
00:03:01,840 --> 00:03:05,440
This is where we're going to type that formula that I just reviewed.
40
00:03:05,470 --> 00:03:15,100
So first step was to use the literal equals pound sign or hash date open parenthesis and then for the
41
00:03:15,100 --> 00:03:25,480
sake of example let's use 2018 comma one comma one that will give us January 1st 2018 and when we press
42
00:03:25,480 --> 00:03:28,950
enter it just basically creates a single value.
43
00:03:29,080 --> 00:03:30,620
1 1 2018.
44
00:03:30,700 --> 00:03:34,860
So that's not very helpful yet on its own but it's a good starting point.
45
00:03:34,870 --> 00:03:39,540
The next step is to click this effects button to add a new step.
46
00:03:39,550 --> 00:03:45,250
And right here where it says equals source this is where I'm going to type that long end formula that
47
00:03:45,250 --> 00:03:46,370
I shared with you.
48
00:03:46,420 --> 00:03:47,880
So follow along.
49
00:03:48,190 --> 00:03:53,020
I'm going to move kind of quickly but go ahead and posit once I enter the whole function so that you
50
00:03:53,020 --> 00:03:55,010
can make sure you've got it written down.
51
00:03:55,060 --> 00:04:09,570
So we're gonna start with list dot dates open up parentheses source comma no dot from open print date
52
00:04:09,660 --> 00:04:13,200
time dot local.
53
00:04:13,500 --> 00:04:18,970
Now quick open and close parented just like the now or today function in Excel.
54
00:04:19,110 --> 00:04:30,930
We're gonna close out one more parentheses minus no dot from open perennial source closed print comma
55
00:04:31,470 --> 00:04:40,740
pound sign duration open print one comma zero comma zero comma zero and then two closed parentheses
56
00:04:40,890 --> 00:04:46,920
to end it now without diving too deep into exactly what this m code is doing in a nutshell what we're
57
00:04:46,920 --> 00:04:52,890
saying is calculate the current date compare that against the literal that we just created which is
58
00:04:52,890 --> 00:04:59,130
our starting date and then based on the difference between those two dates I want you to list out all
59
00:04:59,130 --> 00:05:02,390
of the individual values where the duration is one day.
60
00:05:02,400 --> 00:05:09,360
So it's basically saying give me a list at daily granularity between those dates the literal and the
61
00:05:09,360 --> 00:05:11,370
current date based on today.
62
00:05:11,430 --> 00:05:12,860
So I press enter.
63
00:05:12,990 --> 00:05:13,430
There we go.
64
00:05:13,430 --> 00:05:21,330
It created this whole list and look at this it starts at 1 1 2018 and as I scrolled down through it
65
00:05:21,450 --> 00:05:26,310
ends at 416 which is the current date it's April 16th 2018.
66
00:05:26,310 --> 00:05:32,880
As I'm recording this and if we were to refresh this query a week from now that end date would extend
67
00:05:32,880 --> 00:05:34,350
seven more days.
68
00:05:34,350 --> 00:05:38,780
So we've essentially created this dynamic rolling list of dates.
69
00:05:38,910 --> 00:05:40,300
That was the hard part.
70
00:05:40,320 --> 00:05:45,600
Now all we need to do is click this new list tools tab that popped up and we're just going to click
71
00:05:45,600 --> 00:05:50,900
this button on the left to table and it basically converts it into a table.
72
00:05:50,900 --> 00:05:52,310
There's no delimiter.
73
00:05:52,370 --> 00:05:55,400
We shouldn't have any errors here so we can just press okay.
74
00:05:55,820 --> 00:05:56,390
There we go.
75
00:05:56,390 --> 00:06:01,070
Final step is to format and name the column and then we should be done.
76
00:06:01,070 --> 00:06:05,970
So go ahead and format as a date instead of column 1.
77
00:06:05,990 --> 00:06:12,930
You'd call it something like date and now we've got a single column date table just like we started
78
00:06:12,930 --> 00:06:14,670
with in the last demo.
79
00:06:14,670 --> 00:06:20,280
And from here we could go ahead and add as many of those calendar fields as we wanted to.
80
00:06:20,280 --> 00:06:24,230
So really great tip that I use a lot in my own work.
81
00:06:24,360 --> 00:06:27,780
I manage a ton of reports that have to be updated very frequently.
82
00:06:27,840 --> 00:06:33,000
And this rolling calendar is a huge time saver for working with reports like that.
83
00:06:33,000 --> 00:06:38,940
So for the purposes of this course and this demo with our venture works data we actually don't need
84
00:06:38,940 --> 00:06:40,180
the rolling calendar.
85
00:06:40,290 --> 00:06:43,860
We're going to rely on our A W calendar lookup table instead.
86
00:06:43,860 --> 00:06:48,400
So I know we just did all that work we could keep the calendar here if we wanted to.
87
00:06:48,600 --> 00:06:54,780
Or in this case I'm just gonna right click and delete that query really just want to focus on the one
88
00:06:54,780 --> 00:06:58,550
calendar table that we've got for the sake of this analysis.
89
00:06:58,650 --> 00:06:59,550
But there you go.
90
00:06:59,550 --> 00:07:02,360
Let's close out of the query Ed..
91
00:07:02,550 --> 00:07:04,130
And we're back to square one.
92
00:07:04,200 --> 00:07:07,590
So that's your pro tip how to create a rolling calendar.
9927
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.