How to Cross Join ❌ in Power BI Using Power Query: The Smart and the Dumb Way!

How can you create a Cross Join in Power BI using Power Query? Watch the video to learn:
1. What is a Cross Join?
2. When to Use a Cross Join?
3. The Dumb Way to Cross Join (Yes, I was doing it for years)
4. The Smart Way to Cross Join

Links Mentioned in the Video:

================================
===Most Popular Playlists===

⚡Power On!⚡
-Avi Singh

#PowerBI #PowerBIHowTO #PowerBIPro

[tolulopeesho]

Avi, you will not believe that the dummy method solved my problem, My Crossjoin generated query of over 1 million rows with the second method but got stucked for hours and almost days at the point of filtering for True after text search of column that generated True and False. The dummy method, solved that problem in less than 10 minutes. I decided to try the dummy method when I read Webb's comment and the article he posted.
Thanks so much guys.

[brentpearce]

Suggestion: At 9:50 in the video use this formula for [Date]
let
ThisStartDate = Date.From([StartDate]),
ThisEndDate = Date.From([EndDate])
in Table.SelectRows(Calendar_Dates,
each [Date]>= ThisStartDate and
[Date]<=ThisEndDate)

Now when you Expand [Date] you only get the applicable date values.
Note that the variables ThisStartDate and ThisEndDate had to be created
because the fields [StartDate] and [EndDate] are not visible inside the
Table.SelectRows() function... and at the same time we can do the
DateTime to Date type conversion.

[anandabherath]

Simply excellent and it is a pleasure to listen to you.

[andyle]

The dummy method ironically helped me solve a different issue I was struggling with. Thank you!

[chrislincoln]

Hi Avi, thanks for the video.
Another option for this:
- Merge Calendar_Dates [Date] with Custom Calendar [Start Date]
- Expand the Custom Calendar columns you want to keep
- Select the expanded Custom Calendar columns, right click and fill down

[isabelstroot]

Awesome video. Exactly what I needed to know! THANK YOU

[joaorataoo]

Very good explanation. Simple and unpretentious.
I loved that you showed the 'smart way' and the 'stupid way'

[pl]

Like learning this. Even though I never had an unusual fiscal calendar, I like the methodology I can use for other things.

[jimmythompson]

Awesome video Avi thank you. You are the PBI Grand Master!

[kristjanminnatharmet]

The easiest way to blow it up is with a custom column: {[start date]..[end date]}. Just make sure you have the columns formatted as number before you do that.

[BillionaireLeo]

How to do the same thing in DAX instead of Power Query?

[pieterlinden]

Oh ouch. So just add a constant value to both tables, join on that, and Voila! a Cross join. Then just remove the dummy column.

[johnscott]

I don't see the Custom Calendar in the download

[pabeader]

For the custom column, why not use Conditional Column? It makes the formula for you.

[sebastiendebosscher]

This is the SIMPLE way:

#"Added Custom" =

Table.AddColumn(
#"Changed Type",
"List of Dates",
each List.Dates(
[StartDate],
Number.From([EndDate]-[StartDate]),
#duration(1, 0, 0, 0)
),
type list ),

You're welcome 😉