Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have two tables . PowerBI does not let me join these tables as they do have unique values in one of the columns. In SQL I would join these tables based on two columns.
Table 1
account_id experiment_id
Table 2
account_id experiment_id
In sql I would do the following
select * from t1,t2 where t1.account_id=t2.account_id and t1.experiment_id=t2.experiment_id
How do I create such a relationship in dax?
Solved! Go to Solution.
Hey,
in the query editor
Here is a screenshot:
Repeat this for the 2nd table.
Now you are able to use this column to create a relationship between both tables
Don't forget to "hide" this column from report view.
Hopefully this answers your question
Regards
Tom
I got this to work by selecting the first join columns, then holding control to select the second join columns under "Merge Queries."
I just tried this, and it's amazing. Never would of guessed you can do multiple joins by holding control. Wish this was also available for relationships.
Hey,
please be aware that the hint from the original question, that one column does not contain unique values. This hint indicates that join between tables should happen in the Power BI dataset and not in Power Query.
Joining tables in the dataset means creating a relationship, relationships are important for the performance and filtering the data in the data model. A relationship between two tables of tables can only use one column
Joining tables inside Power Query means creating one table from two tables. This kind of join is called merging tables. A merge inside Power Query can be based on multiple columns between the two tables.
Each type of the above mentioned tables joins is pursuing a different goal.
Regards,
Tom
I should clarify that my response was related to the Merge Queries function in Power Query, not creating relationships in the data model after loading queries. I read the question as a SQL user where Join is the equivalent to Merge in Power Query. I had previously created concatenated columns like you suggested in your solution but didn't like creating a new unnecessary column. I didn't know that you could merge queries based on multiple like columns in Power Query, but you definitely can in SQL. And so I tried holding control when selecting the second ID column (e.g., Customer ID as the first column, Month as the second column) in the "Merge Queries" interface, and that worked to Merge the two tables together based on values from two columns. It achieves the same result as this:
JoinedData = Table.Join(AgeData, {"Customer ID","Month"}, OccupationData, {"Customer ID","Month"})
I agree that you would not try to achieve the desired result using DAX.
This worked for me. Good tip - Thanks.
Hi All,
Just wondering if merging/concatenating columns is the only way to join tables based on multiple columns?
As this creates additional columns which is slowing down and making my report heavier.
Any update please?
Thanks
Robin
@Anonymous - did you see the post from @MichaelHenry from 3/8/2020? All you have to is pass arrays of column names for the key parameters like in the example @MichaelHenry provided:
JoinedData = Table.Join(AgeData, {"First","Last"}, OccupationData, {"First","Last"})
This thread is the first result that appears in Google when searching for "M join tables on multiple columns" so it needs to be kept up to date. The accepted answer is wrong - it is possible to join using multiple keys. I assume at the time of the original response (2017) this was not the case. (Yes I realise the original question was asking about DAX but the accepted response was in M and that is what I followed until it was pointed out to me that I could use multiple columns).
Here is an example demonstrating a join on multiple columns:
let
AgeData = Table.FromRecords(
{
[First = "Casey", Last = "Holmes", Age = 20],
[First = "Jon", Last = "Henry", Age = 28],
[First = "Phyllis", Last = "Hassan", Age = 32],
[First = "Dua", Last = "Myers", Age = 33],
[First = "Cody", Last = "Anthony", Age = 40],
[First = "Sebastian", Last = "Currie", Age = 40],
[First = "Jaspal", Last = "Chadwick", Age = 41],
[First = "Suhail", Last = "Ryder", Age = 42],
[First = "Fabian", Last = "Maldonado", Age = 45],
[First = "Yara", Last = "Ellison", Age = 58]
}
),
OccupationData = Table.FromRecords(
{
[First = "Cody", Last = "Anthony", Occupation = "Bricklayer"],
[First = "Fabian", Last = "Maldonado", Occupation = "Business Consultant"],
[First = "Dua", Last = "Myers", Occupation = "Clerical Assistant"],
[First = "Phyllis", Last = "Hassan", Occupation = "Estate Agent"],
[First = "Yara", Last = "Ellison", Occupation = "Interpreter"],
[First = "Casey", Last = "Holmes", Occupation = "Museum Curator"],
[First = "Jon", Last = "Henry", Occupation = "Nurse"],
[First = "Suhail", Last = "Ryder", Occupation = "Police Officer"],
[First = "Sebastian", Last = "Currie", Occupation = "Professor"],
[First = "Jaspal", Last = "Chadwick", Occupation = "Refuse Collector"]
}
),
JoinedData = Table.Join(AgeData, {"First","Last"}, OccupationData, {"First","Last"})
in
JoinedData
Hey @MichaelHenry ,
please be careful with stating the accepted answer is wrong.
The question was not asking for DAX, it was asking for a solution that could be used in the data model. Creating columns inside Power Query is recommend as all columns will be compressed. Calculated columns using DAX will not be compressed. For this reason I created the columns inside Power Query.
In my original post I mentioned, that the created columns can be used to create the relationship.
It's important to understand what is required
Regards,
Tom
I also came here from the google search results. I'm glad I scrolled down to this post. Hopefully my reply helps bump this for the Microsoft team or something
@MichaelHenry Thank you for posting this! Did you find this in Microsoft's official documentation? If so, would you be willing to post a link to the page?
Thanks!
You can acually do this in the merge query editor. If you press control while selecting columns there is a small digit indicating the order displayed for each column allowing you to join on as many columns as you want.
/Cheers
Hey,
currently it's not possible to create a relationship in Power BI between tables that spans more than one column.
For this reason I concat both columns in the Query Editor in each table, and use this column to create the relationship.
Hopefully this gives you an idea
Regards
Tom
@TomMartens: Thanks for the reply. I did not understand it completely. Could you give me example and tell me how the output would look? Will the concatenated column have unique values?
Sorry I am new to this.
Hey,
in the query editor
Here is a screenshot:
Repeat this for the 2nd table.
Now you are able to use this column to create a relationship between both tables
Don't forget to "hide" this column from report view.
Hopefully this answers your question
Regards
Tom
OMG this was the reason I advocated for PBI, and now it's no better than any other tool. Way to go MS, for taking away the one reason PBI stood apart.
This worked great for me, thank you!
Could you please confirm whether or not you can use these steps with a Calculated Column? It doesn't look like on my end, but maybe I'm missing a step.
@TomMartens
Thanks for guiding.
Can you please tell me how will we design Incremental load with Merged Query.
In my case, I have to join tables with outer joins, I can't done it with concatenation, If I join the tables in merge query, than how I manage the Last_Updated_Date.
Should I create the Reference of tables and than merge the referenced queries using merge query and configure the incremental load on the original loaded tables?
What is the correct way to do it?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
105 | |
98 | |
39 | |
30 |