March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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
Solved! Go to 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
Proud to be a Datanaut!
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
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
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
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:
Pete
Proud to be a Datanaut!
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
96 | |
87 | |
71 | |
62 |
User | Count |
---|---|
138 | |
115 | |
115 | |
99 | |
98 |