00:00Hey friends, welcome back.
00:07Now in the last video at the end, I reopened the Power Query editor to show you that we
00:12can dive back into it and continue cleaning and transforming our data, but also because
00:18our model setup is not finished yet.
00:22What do I mean by that?
00:23Well, that is a good question.
00:24The issue here or what's missing currently is a dates table.
00:29Dates table is something very, very important in Power BI when it comes to any kind of time
00:34intelligence.
00:35Time intelligence means, for instance, you want later on in your reports, compare the
00:42this year sales to prior year sales, for instance, right?
00:44Or this month's sales to prior month's sales.
00:47So any kind of time calculation based on the measures you have in Power BI, they require
00:52a proper dates table.
00:55And for that, I have something which you can download from the resource section and follow
00:59along, which is a custom M code.
01:02M is the query language inside the Power Query editor.
01:07What's meant by that is, let me just show you, let me just open the code I have prepared
01:12for you, which is this one here, okay?
01:15So I totally get that it looks kind of intimidating, at least at first when you see it the first
01:20time.
01:21But what it actually is, is a function written in M. And this actually, all these codes,
01:28so this M code, this is also created for all the transformations we do.
01:33So let me just quickly show it to you.
01:35So for instance, for the sales here, this transformation we have done by combining these
01:40three tables under the hood, actually M code was generated in order to do this.
01:47So we use the front end here, so we can simply click on specific transformation steps.
01:53But under the hood, what Power BI is doing, it is executing M code.
01:58And if you would like to see what kind of code it is to create the sales, you can simply
02:03go under home to advanced editor.
02:06And now you see what actually was the formula which Power Query used in order to create
02:11the table.
02:12So there's a function in M which is called table.combine.
02:16And then it requires inside curly brackets the name of the tables.
02:22Now all these codes here, which the code is generated, you do not need to know it.
02:28I mean, if you would like to dive deeper into M to write your own M code, you can do that.
02:33But to get started with Power BI, this is not required.
02:37Because the transformations are done for us by Power BI and the complexity is abstracted
02:43away because we can simply click on specific icons here to do transformations.
02:50But it's great that you can see that, for instance, these applied steps, source, navigation,
02:55change type, and so on.
02:56If you select a table, you go to the advanced editor, then you see that what actually gets
03:01executed under the hood.
03:03So a connection to the Excel workbook, in this case, the selection of the source, the
03:08transformation of the data types of the columns, all this is in here.
03:12So if you like to see this and like to learn more, then this would be a good option to
03:16simply select the table of transformations and then go to the advanced editor to take
03:20a look at that.
03:21But if you do not want to do that and it looks quite complex, no worries.
03:25Because as I said, there is no need to do it.
03:28You have the interface and we can do all the complex transformations in here.
03:33For a dates table, however, we're going to use something which is already prepared.
03:37This one.
03:39And don't worry, it's quite simple because all we need to do is simply copy everything.
03:43I press control A in here, control C to copy it, and then I'll go back to my model in here
03:49and I'd like to add a new source.
03:52So I click on under home, new source, and then I create, instead of connecting to a
03:57specific file, I go to blank query.
04:01The blank query means that, again, I go into the advanced editor, which I've already shown
04:06you, and here I can write my own query.
04:11And there's a default syntax inside.
04:13I remove this and then I just control V to paste everything I copied.
04:20It's quite a lot, but it creates a lot of columns for us.
04:25So you see that no syntax errors, which is good.
04:28So let's click on done.
04:30And now what happens is a function gets created in here.
04:34This function requires a few parameters like the start date, the end date, and the financial
04:39year start month.
04:40Now, in our case, we have three years, which is 2021 up to 2023.
04:47This is why as a start date, I can simply type in here, that's the first January of
04:532023.
04:54So first, and the end date is, oh, sorry, 20, be careful, 2021, of course, the end
05:03date is in this case, the 31st of December in 2023.
05:10So this makes sure that I have the data for all my three years.
05:14And then if I would have a fiscal year, which is different from starting in January, for
05:20instance, my financial year starts in April, anything like that, or in May, then I could
05:25actually add the month number in here.
05:27For us now, it doesn't make any difference.
05:29So I simply type in a one.
05:31And if you have a holiday calendar, you could also choose the holiday calendar, then this
05:35holiday would also be implemented.
05:38I don't have this, so I just give it, but it's also available inside the M code, which
05:43you can again download from the source section.
05:45So this one I've shown you, this one, okay.
05:49So we copied it, we specified the date range we want to create, and everything we need
05:55to do now is simply click on invoke.
05:58And now we have a complete new table created, this called invoke function.
06:02So let's actually rename this properly and say, this is our dates table, call it dates,
06:07press enter.
06:08And now we have the dates.
06:11And what happened now is that all of these columns in here have been created with this
06:16M code first.
06:17And you see that we have a date column in here, which is starting in 1st of January
06:222021, up to, of course, then the 31st of December 2023, because that's what we specified.
06:29And you can see that all the columns we have in here, we have here also the month names
06:34in here, we have an abbreviation with the month name and the year, we have here the
06:39weekday names, even though they are in this case in German, because it's my native language.
06:43But of course, for you, it would have shown in your native language, and all the other
06:47kinds of fields in here.
06:49So you see, there are a lot of specified fields, which you can use in order to slice and dice
06:53our data.
06:54But much more important than just slicing and dicing is that this table here allows
07:00us to do time intelligence in Torbjörn when we create our first measures.
07:04So as I said, a comparison between different years, for instance.
07:09And the reason why it is best practice to use a separate dates table like we do here,
07:14instead of relying on a date column, which we already have in a model.
07:19For instance, in sales, there is, of course, also an order date column, right, this one.
07:24Now, of course, we could try to use the order date column for our time calculations.
07:29But this would not be a good idea.
07:31And the reason for this is, we cannot make sure inside the model.
07:36So let me just take this inside the model that we have actually reflected each day in
07:41this data set.
07:43So for instance, here, I can see it starts here with the 23rd of January, sorry, of February
07:492023.
07:50And then I have the 24th.
07:52But for instance, what if the 25th is missing?
07:55So I cannot guarantee that on each day, in our three year period, I have a sale.
08:02And because I cannot guarantee this, that also means that the order date that might
08:07have missing dates, because maybe we have a date, and on this specific day, nothing
08:11was sold.
08:14If our calendar is not complete, so has missing dates, then there might be the case that the
08:21DAX, which is the Power BI query, Power BI language, when we create our formulas, that
08:26the DAX calculations are wrong.
08:28So they give wrong results.
08:30So we need to make sure that we have a proper dates table, where no date is missing in the
08:35date range we want to analyze.
08:37And only then, we can ensure that our calculations are always correct.
08:42And that's why it's so important to have a separate dates table, because with this dates
08:46table here, which we created, we are ensured that no date in here is missing.
08:52And this allows us then to do or base our calculations on this specific column.
08:57That is why it's the best practice to have this separate dates table created to make
09:02sure that we have perfect all the dates, okay?
09:07So then, let's actually drag this also inside our model view, because I want to use it in
09:12here.
09:13So that's fine.
09:14And for the query, let's actually drag the query down, because query itself was just
09:18for creating the table.
09:19We don't need it anymore.
09:20And it's not part in here of the model itself.
09:23For the query, there is no need to right-click and somehow disable the load, because the
09:29query itself is just a query.
09:30It doesn't create any table, as long as we do not create some kind of parameters here
09:34and invoke it, right?
09:36You can also see it simply by the icon, it's simply a function, right?
09:39It is not a table itself, so there's no need to enable the load or disable the load on
09:44that.
09:45Okay, so that's it for the dates table.
09:49So again, I'd like to mention, this is very, very important, okay, regarding any kind of
09:53time calculations and time-based calculations in Power BI and DAX.
09:57You want to have a dates table which reflects all the dates, so there are no missing dates.
10:01That's very, very important, okay, to ensure that the calculations are correct.
10:06So if you can guarantee that in your native data you already have, and there are all the
10:11dates inside, you might also go with this one, but as I said, as a best practice and
10:16for all the projects I have done so far, I always use a dates table.
10:22And just to mention this, maybe you'll see this in the future, the dates table itself
10:27does not have to be created inside the Power BI editor with this function, there are also
10:31other ways to do it.
10:33There's also an option even to create a dates table with DAX formulas in Power BI, but in
10:38this case, I'll just use this formula here and create a dates table this way, okay?
10:43So as always in various products, there are always more than just one way to skin the
10:47cat, but this would be one way.
10:49And the great thing here, I'd also like to point this out, is you can reuse this formula
10:54all the time.
10:55So not only for this specific course here, but also for all your future Power BI projects.
11:00You can simply use the code we have seen, go here, just ctrl-c, ctrl-v to copy it, paste
11:06it in here, as in this case, under new source, go to blank query, and paste it there inside
11:11in the advanced editor, and then specify the date range you want to analyze, and then use
11:15it in the project.
11:16So at the same time for all the projects you have, okay, there are no other things to do.
11:22So hopefully that is helpful, and that's it for this video, thanks a lot for watching,
11:27and I'll see you in the next one.
11:29Until then, best guys.
Comments