Crossjoin in PowerQuery vs In table

[vipett]

Hi,

I am doing a lot of cross joined tables with just Table = Crossjoin(Table1,Table2,Table3,etc), sometime with filters or only selected columns.

I wanted to try doing it in PowerQuery instead, with data from a Dataflow form the PowerPlatform.

One example I did with three tables that contained 2-5 columns per table with table1 17 rows, table2 about 390 rows and table 3 about 25 rows

To do the crossjoin in DAX took just seconds to generat the 165k rows.

But If I do it in Powerquery (adding a custom column and expanding it) it is loading super slow, so far it has loaded about 400kb in 15 minutes.

No internet connection issues, I can load massive tables from our DW in no time.

What am I missing, is PowerQuery doing something strange and asking the powerplatform for each and every row?

Is there a better way of doing it?

I did try to do it in a dataflow using the website, but then it wanted to load 1gb+ each time which is way way more than what it should be.

[amitchandak]

@vipett , refer

https://learn.microsoft.com/en-us/power-query/cross-join

[vipett]

Hi,

That articles describes the two methods I've tried but doesn't explain how it works in more detail, so that I can understand what method to use in different scenarios.

Using DAX is quick to generate it, but with calculations after it becomes really slow

Using the expand method is super slow to load, but faster with calculations.

Is there another way to do it?