Join/Pivot Imported table with Calculated table.

[StuartSmith]

I have 2 tables (as an example), the first table is imported in data and the 2 table is a calculated table.  

How can I join and pivot the tables so the end result looks similar to this... so i have a "Region, Country and Carrier" line for each keyword.  

Demo file: https://www.dropbox.com/s/c7e6ky1j9hl9x7l/Join%20Piviot%20Calculated%20Table.pbix?dl=0

[BA_Pete · Accepted solution]

Hi @StuartSmith ,

Looks like you're trying to create a crossjoin.

Create a new table in the Data view using the following code:

crossJoin = CROSSJOIN(Sheet1, 'Key Words')

This gives me the following output:

Pete

[StuartSmith]

Thanks, one question, if either of the "Sheet1" or "Key Words" tables are updated, woould this updated be reflected in the "Join" table?

[StuartSmith]

Just tested and yes it does 😁

[StuartSmith]

Looking at htis again, ideally, i only want to use the "Region" and "Country" columns from "Sheet 1" to join with the complete "Key Words" table.  And as the Join Table doesnt show up in query editor, I am unable to delete the unwanted columns.

I tried something like

crossJoin1 = CROSSJOIN(VALUES(Sheet1[Region]), VALUES(Sheet1[Country]), 'Key Words')

but thats producing too many rows.

[BA_Pete]

@StuartSmith ,

FUN FACT: If you want to implement this in Power Query (Query Editor), then all you need to do is create a new custom column in one of your tables that you want to crossjoin, then type the name of the other table in as the column code.

This should output a column full of [Table] values - just expand this column and voila, Power Query crossjoin.

Pete

[BA_Pete]

Hi @StuartSmith ,

Try this as a new calculated table instead:

crossJoin2 = 
CROSSJOIN(
    SUMMARIZE(
        Sheet1,
        Sheet1[Region],
        Sheet1[Country]
    ),
    'Key Words'
)

This gives me the following output:

 Pete

[amitchandak]

@StuartSmith , Based on what I got. Crossjoin in DAX

crossjoin(Table1,Table2)

https://docs.microsoft.com/en-us/dax/crossjoin-function-dax

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Check for any merge option in power query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query