Join is really about smashing these two tables together and you get a combination of all of these rows. So here as you can see that we had a vanilla and a kind of a list of manufacturers but here we have vanilla and a combination of each of the manufacturers so i also sometimes call it like blowing up the tables because it really kind of multiplies them so that's a cross join now let's talk about a specific example so ice cream well maybe that'll make you hungry but.
Where do i use it in power bi obvi so for this example i have to thank one of our learned power bi members byron who asked this question in our monthly talk power bi q a call and you can join us on talk power bi live shows every friday where we hang out and answer questions and talk about power bi so he brought us this custom calendar table now custom calendar table is something that i get truly fascinated about because it's one of those things which is really hard in like.
The before power bi era but with power bi it makes it super easy to deal with custom calendar custom calendars are special because they have these weird month boundaries like for example you can see this example by national retail federation that uh let's take uh 2021 uh and and there the month of february or the period of february actually has january 31 in there so again i mean as you can see like i mean a month isn't really a month like here march actually has.
April dates in there what is consistent is this four week five week four four-week pattern now this is obviously intentional because it makes year-over-year comparisons and such kind of rigid and and it makes sense so it makes sense for the companies that are using it but again it can be a true challenge to deal with it to report based on it but now if you're using power bi so custom calendar table the one that byron brought in had a fundamental problem though which is that.
Description below on mobile just tap on the title to expand and see the description so how do we take byron's calendar and solve this problem so he has a start date and an end date how do we go from there to a list of contiguous dates all right now well you might have guessed it we are going to do a cross join but crosstalk with what we're going to smash it together with basically a regular list of dates that's what we're going to do now that is going to give us the date column and the.
Contiguousness now folks here i'm going to take my time and go a little bit deeper because i believe that the missing step that a lot of people don't have in learning power bi is what i call a thought process now a symptom of this is is that you've been spending a lot of time learning but you still get stuck when it comes to doing and that's because you haven't quite gotten this missing step which is to get the thought process so i'm going to go a little bit deeper not only.
Give you the solution but give you my thought process as well now if you want to learn more about this principle of missing step i covered it in detail in my video again i'm going to link it in the corner and down in the description below go watch that it also talks about the five key steps you need to master power bi all right so let's jump into power bi and i'm going to show you the dumb way to cross join and the smart way to cross join so here we have in power bi and i'm gonna.
Click on transform data and you can get this file by going to uh the link in the description all right so yep it's on the screen as well really easy link and here is our query editor and again we have an example of the flavors manufacturers and the cross join but what i want to show you is the calendar example so here we have the custom calendar coming from byron which has the start date and date pattern but we need contiguous date right so we need like one.
Three two one january third january fourth and so on so forth with the fiscally appeared and we do have the ultimate calendar table we're not going to use uh much of it at all i'm actually just going to reference it and so i'm going to reference the calendar and i will basically i just want i'm just interested in a list of dates so calendar and now of course i could have just simply built it using list states.
Posts Related:
- Excel Today - VLOOKUP and XLOOKUP
- How To Create A Powerful Point Of Sale (POS) Application In Excel Full Training & Free Download
- How To Design A Responsive Portfolio Website Using HTML, CSS & JavaScript Part 8 BlogSpot
But you know i have the ultimate calendar so might as well use it so again i'm just going to - ID Card Make
Keep the date and remove other columns so this is my calendar date and i don't really want to load these tables back so i'm going to uncheck the enable load so again we have the custom calendar table and we have the calendar dates and are you ready to smash them together and do a cross join so i'm going to show you the dumb way to do it so the dumb way.That i would do is i would add a column and add a custom column and i call it the dummy column and it is a dummy column as you're going to see so i'm going to add a custom column uh call it dummy and and just the value doesn't matter i'm just going to put one here and i'm going to do the same thing in the other column calendar dates again got a custom column right here and the same thing dummy and one hit okay and then i would do a merge based on this column so watch what happens i'm going to.
Go to home and merge queries right there submerge queries and i'm going to take this custom calendar and smash it with my calendar dates based on the dummy column and again since the dummy column is always one it's going to match for every rows that means it's going to take multiply the rows basically right as we saw in the example so we started off with 84 rows here and now once we have the merge i'm going to come in here and expand and i just want the date column.
So that's the only one that i'm going to keep and hit okay and let's see what happens here so i'm gonna just to show you i'm gonna sort the start date ascending and then sort the the date coming from the calendar column and you know what i'm gonna put these side by side to see see what it did so basically earlier before we merged it we had a single row for january 3rd and january to january 30th indicating this range but now that has been.
Multiplied by every single row in a calendar table and our calendar table starts in 2014 and i think goes on to like 2020. so as you can see here it has each of those rows right and that that's of course that's across join so you can see we keep going 2014 and 15 and so on so forth right so again until 2020. so that's a cartesian join now of course so this is like a blow up often when you do a cross joint like this after that you also want to filter down to valid rows so.
I'm going to show you and if you're not getting it you're going to get it then so i don't want to keep all the rows i only want to keep the matching rows which match this date range right so this has been multiplied with all the calendar date rows but i only want the valid rows so for that i'm going to do a custom column actually you know what uh let me backtrack a bit and instead of doing it first let me show you the smart way so this was the dumb way and i guess um you know.
There was a reason i called it a dummy column uh i didn't realize i was a dummy all right so uh let's backtrack all of this and turns out you don't need any of this you don't need the dummy column on this side on that side so i'm going to show you the smart way to merge and then we'll solve byron's problem about getting a contiguous calendar so you go to custom calendar and if you want to kind of cross-join these two tables all you have to do is add a custom column.
And put the name of the other table there so this one is gonna be i'm just gonna say date and calendar date so dates sorry so again you just put the name of the table you want to join and that's it boy it couldn't have been easier and and that is your cartesian joint so each one gets a table and then you expand it and you're going to get the same result as earlier right so again you can see the january 3rd this range has been repeated for a lot of these days all the dates in.
The calendar table great so now let's go back and continue our progress here so we wanted to filter down to the meaningful dates only so we'll say match date range so we're going to check we're going to check the date column right and we're going to see that hey does this fall into the start date and end date if it doesn't we don't want to keep that row we right i mean we only want really january 3rd to january 30th right so starting here.
And going on to the 30th we only want those rows to match this one and that's how so yeah we want
To we really didn't want to cross cross-join everything we only want to do selectively but really i don't know any way to do that except to cross-join and then filter all right so if you have the smarter way then let me know in the comments but here uh it's really really easy so we'll say that if the date column is greater than equal to start date.And the date column is less than equal to end date then oops uh then true um else false right so there we go simple as that and i'm gonna hit okay oops uh hold on all right so the the uh the d types are not compatible one is a date time as i can see by the icon here these are date times i can also see that in the time here whereas the the date column is date only so before i add the column i'm just going to change these to date i mean the time is 12 anyway.
And or i won't have to change the formula now it's just going to work so date perfect i changed both of these and if we go back to the earliest step we had added which was comparing starting and end date now notice that it is working so i have to scroll way down to show you that it is actually working so if you look at here again the range was january 3 to january 30th 2016 and we only wanted to expand.
It to those dates and you can see here that you know anything before that range is is kind of false right and and then uh once we start matching that's when it is true so that's it that's all we need to do we matching date range we only keep the true value and now we actually do not need the start date or end date i mean you can use it for debugging if you want but you can remove the start date end date and date range these are throw away.
Columns put it in the trash can off the power bi kitchen and now we have the same custom calendar right so again his custom calendar had defined this range about saying that oh january 3rd to january 30th is this fiscal period right and fiscal period one and now we have the the same thing oops sorry i just want to go to the last step but in a manner where the dates are contiguous that means this can be used effectively in power bi as a custom calendar.
Table so folks as you saw it takes a little bit of a time to dig a little bit deeper and go into not just the solution but the thought process that's going into it but again if you are stuck in this point where you're you know kind of learning and you're trying really hard but you still keep getting stuck when it comes to applying it to your own data set to your own reports i know how frustrating that can be then make sure to check out my missing step video and learn about.
