Microsoft Power BI Time Intelligence 2024 by expertiseexcelerate - Dailymotion

expertiseexcelerate

Watch the playlist Microsoft Power BI Time Intelligence 2024 by expertiseexcelerate on Dailymotion

Transcript

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.

Time Intelligence in Power BI

Category

Transcript

Microsoft Power BI Time Intelligence 2024

Recommended