In this video I'm going to show you how you can do a cross join in Power BI using Power Query
or the Query Editor so I'm going to take you through these four steps, we're going to talk
briefly about what is a cross join I'm going to give you a specific example of when you may
need a cross join and I'm going to show you a dumb void across join that I used to do for a long time
and then I discovered the smart way and that's the one you should use so are you ready to go
hey but let me first introduce myself my name is Avi Singh I'm a Microsoft MVP and a best-selling
Power BI author and if you want to become a Power BI Pro then make sure to click subscribe and yes
click that bell so you are notified whenever we go live to answer your Power BI questions.
All right so let's talk about cross join so what is a cross join so he here we have an example of
a table of ice cream flavors yum yum and the where we have ice cream manufacturers so cross
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
it doesn't follow the universal laws of power bi calendar tables which is it has to have a
date column with the contiguous dates he didn't have that structure he had this start date and
end date as you can see kind of right here right so it doesn't follow that structure by the way
if you are interested in learning more about the regular calendar table i cover that in my power bi
ultimate calendar series and you can catch that by clicking on the links in the corner or down in the
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
but you know i have the ultimate calendar so might as well use it so again i'm just going to
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
the key step and also the five other steps to master power bi and again you can find the links
in there and uh keep watching our next videos and don't forget to subscribe until then power on
you