Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StuartSmith
Power Participant
Power Participant

Join/Pivot Imported table with Calculated table.

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

 

2021-01-20_16-32-04.png

 

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.  

2021-01-20_16-30-54.png

 

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

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1611162238175.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
BA_Pete
Super User
Super User

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:

BA_Pete_0-1611162238175.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

Just tested and yes it does 😁

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.

@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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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:

 

BA_Pete_0-1611229983563.png

 

 Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




amitchandak
Super User
Super User

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.