All language subtitles for 005 Date Master Tables_en

Welcome to this lesson.

So in this lesson, we're going to be speaking about a data master file.

The idea of that master file is that it's going to have already pre configured dates in here.

So this would mean that we don't have to go into each report and create like we have done here a year

field, a month field, the day field and so on, which obviously takes quite a bit of time.

So what we're going to start off by is just in this data model, we're just going to remove these existing

fields.

So one way you can do it is you can right click on the field name and you'll see that there is an option

to delete that.

And you just agree that you want to delete it out of the model.

You can also go to the field itself and you can actually click on the delete key and it will also give

you the option to be able to delete it.

So I'm going to do that.

I'm just going to select each of these fields.

Click on the delete key and we're actually just going to remove those from the data model.

So I'm going to keep myself going to keep my profit, but I'm going to remove my month.

Take that out.

Take our day out.

But we are going to keep our cost.

So we're going to go back to just having our cost, our sales and our profit as our calculated column.

So we're going to add a new Excel file.

But before we add the new Excel file, what I'm going to do is I'm just going to open this in Excel

and we're going to have a look at the structure of this.

So I'm just going to explain how a data master file should be created.

So I've got the data master file open now in Excel, and you can do the same from the course files that

you've downloaded.

Okay.

Let's start off with understanding a little bit of how this needs to be created.

So you can see that I've got my first field.

I've called this master data.

You can give it whatever names you want, but just maybe make it a data that's easy to sort of recognize

and remember.

Now, the first rule is that when you're creating these data files is you must make sure that your date

master has a date that is at least equal to or before the starting date that is in your own reports.

So you've got to make sure that the data is at least equal to or before the starting date of the data

that you are using in your report.

And the same as for the end date.

And you can see that these dates go quite far and I've actually taken them into the future.

So we go to the end of 2024 here and you can see that each date well now encompasses the date range

of the data that you're working with within Power BI.

So that's the first rule is that you've got to make sure that the range of dates incorporates the dates

that are actually in your data file.

The second rule is, is that each of these master dates must be a sequential date.

So normally the easiest way to do that is to create the first two dates.

So if you created the 1st of January 2018, then 2nd of January 2018, if you select both of these and

you go to your autofill and you just drag this down, it will then create the subsequent date for you.

So automatically autofill those dates in.

So just make sure that these though are chronological, that they follow each other.

Please make sure there must be unique.

You cannot have the same date twice and then also that each data is covered.

So those are some of the rules that you need to have, is that you need to cover those master dates.

Once you've done that, you can now create your own fields.

So I've created some examples here.

What we started off with was the day.

So this is just the day of the date.

And you can see that we've used the Excel formula called Equals Day.

And again we got the month number and you can see that we're using the Excel formula equals month.

We got the year, the weekday, the month, the weekday name and the month name.

Now I'm going to go through these in detail and you can have a look at the file and you can look at

the Excel formulas that I've used.

Now, obviously, you can create your own.

One thing I do want to note, there was a lot of people ask about how you do your own fiscal periods

within power by.

Now, this is one way you can definitely do it because you can now map your own fiscal periods within

this file.

So you could add a new column into this.

You could call it your fiscal periods and you can then map as to what this date is against your own

fiscal periods.

And obviously, once you brought it into B, you would then be able to use those fiscal periods.

So that's one example that you could use.

Okay, we're going to close this.

We're going to bring it into Power BI and we're just going to see how we set this up to be able to use

it.

Okay, so we're back in our Power BI desktop.

Let's go back to our home menu.

I'm going to get some new data and we're going to go to our Excel workbooks.

So select Excel workbooks.

So please navigate to where the Udemy resource files are and you're looking for the data master file,

so please select the data master file.

Going to click on the open and it will then do the process that we've seen previously.

It opens up the navigator.

In this case, you'll see that it picks up three sheets of data.

We're just going to be using the first sheet of data, which is called dates can select on that to see

the preview of the data that we've just seen in Excel.

And again, we're just going to load that data straight into our Power BI desktop.

So again, it's going to load that data and you can see now that we're going to have a table called

data, I'm just going to collapse that.

We've also got a date table called date.

And what we're going to be doing in this lesson is we're going to be looking at relationships.

I'm going to be looking at relationships later on in the course as well.

Just its own small little section.

But we're going to need a relationship between these two tables to be able to use our date master.

So let's have a look and see how we would do that.

Now, if we looked over here on our data table, we'll see that we've got a sales date.

So we have a transaction that happened on a specific date.

If we go across to our data table, we've got our master date now in here.

If we were to sort this, let's see if we can actually sort this ascending.

So from earliest you can see then that we would be able to find the master date of that date transaction

that we use for our sales date.

So basically now that we've got the two tables loaded in our power BI, we want to create a relationship

between those tables.

So we're actually going to use this third option called the model.

And you'll see now that it's got both of my tables being shown in the model.

Now, the easiest way to be able to create a relationship is you actually just pick the two fields that

you want to use.

So say, for example, in this case, I want to create a relationship between sales date and master

date.

All I do is I click on the sales date.

I drag it across and you'll see that I'm allowed to go on top of the master date.

So drop it in there.

And you can see now that it's now created a relationship between the two tables.

Now, very importantly, it's telling me a one here.

So what is saying is that there can be many transactions, but there can only be one master date.

And we said that was very important.

When you're setting up your date's master table, is that the master date must only have the date once.

Cannot have it more than one cannot have duplicates.

Otherwise the system will not know which transaction here it would be linking to in which table.

So basically you need to make that.

And you can also see it's got the direction of the relationship here.

So here we got on many.

Here we got a one.

As I said, we're going to cover relationships a little bit later.

But the main part is we've now got that relationship between the two tables.

So now if we go back into our report, let's create a table.

So if we drop a table into our report and you'll see we've obviously got our two tables.

So what we can do now is we can pick our dates and if we picked a master date and dropped it in there,

you'll see now that our master date is there, we can now actually put our sales and you'll see now

the sales is showing me for the master.

Let's actually just make this the most date field.

Let's not have the hierarchy.

So there we go.

We've now got let's actually just sort this as well so you can see the different dates.

There we go.

Now we got our sorting.

So by the master date now, which is in one table, we've now got the summer sales from a different

table.

And that's really what relationships is allowing us to do, is be able to cross between these tables.

But what it does mean now is that I can use any of the fields in this date.

So let's change this a little bit.

Let's say instead of using our master date, let's say we want to use our year.

So we drop our year and there so you'll see.

Now it's summed up the year so we know from previously we want don't summarize and there we go.

We've got our year.

And what I might well want to do in this case is actually go to year and change the default method of

summarization to don't summarize.

So hopefully in the future it won't do that.

And what I could do is that on a month will probably do the same.

So we'll say don't summarize on that.

We could also say that they will do the same.

Let's don't summarize on that.

Okay.

So that should hopefully create that.

Let's change this into a matrix.

So let's have a year and let's put on month number.

So let's go to the month and we drop it that into our columns and you'll see straight away.

Now we've got all our month numbers like we did previously.

We go to all of our years and you'll see that the calculations will be exactly what we saw previously.

Okay, let's just delete this.

I just want to show you another example.

Let's go back now to all week day.

So we got a weekday.

We saw this in the previous lesson.

So take our sales again from the other table.

And again, we can see we got a weekday, we got the sales figures and that's working perfectly.

But again, we've got the problem that we don't know what is the actual weekday name.

So let's create a new table.

Let's put our weekday name in there.

So we drop that in there.

I'm going to drop it all week, then I'm going to drop off sales.

And again, we're going to see that we have this problem where the days are not in the correct order.

And if I change the sort order of this, it's just going to change it alphabetically.

Now, what you'll remember is that what we did for this was we clicked on weekday name.

We went to our sort bar column and we actually said that this is going to be sorted by the weekday number,

which is etched in this case or weekday field.

Select that.

And now you see that the order has changed to be correct.

You'll also see this with all month name.

So again, I'm going to create a table.

And to put a month name in there.

So we got our different month names again.

You can see they're not in the correct order.

Drop the sales figure in there.

And again, now I'm going to go to my month name.

I'm going to go to my soap bar column.

And I'm going to now say I want to sort it by the month number.

Select that.

And you can now see that I've got the month names in the correct order.

So there we go.

That is how we can use a date master.

We can bring it in and you can see that I've just used the same functionality that I used in the previous

lessons, but I could now easily bring this into other reports to set up the relationship, set up the

the fact of getting the sort bar correct and then I can use all those dates.

So probably a lot quicker than recreating all of these fields each time.

Okay.

We're going to conclude the lesson there.

We'll see you in the next one.