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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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