Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
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?
What if in the two columns merged there are values 1 and 11, and the same in the columns merged in the second table? There will be an incorrect join for 1-11 and 11-1.
This is new for me, and works perfectly!
Tks
You can do this in Power Query with Table.Join or Table.NestedJoin; as they are, these functions will join on a single column only; you can circumvent this, by replacing the Table parameters with Table.AddColumn, which can add - on the fly - a new, composite column, to join on, something like:
JoinedTable = Table.NestedJoin(Table.AddColumn(Table1, "CompositeColumn1", each [JoinColumn1] & [JoinColumn2]), "CompositeColumn1", Table.AddColumn(Table2, "CompositeColumn2", each [JoinColumn1] & [JoinColumn2]), "CompositeColumn2", "NewCompositeColumn"),
Hi @LON Thanks for the feedback, must admit I'd forgotten all about this, mainly because I'd worked around it, doing something similar to what you describe, using a DAX formula.
Regards
Fred
What an excellent idea!
Unfortunately, this method doesn't work if your input tables are in DirectQuery mode. When you try to merge, you'll get an error message and an option to transform the data input to Input mode.
So, new to Power BI but this is something I'm trying to achieve, using MSProject as input. How can I tell if the tables are "in DirectQuery mode" and how do you "duplicate" columns, is it simply a matter of adding a New Column and setting the formula to “DupCol = Col-I-want-to-join”
@TomMartens: I am now able to create a relationship between the 2 tables but somehow the filtering is still not working. Any ideas?
I'm having a similar issue.
I'm trying to merge together the tables so that Ican use two layers in a ArcGIS Map in PowerBI.
Data is broken out per the below:
Table 1: Brand Index
Col1 - Brand
Col2 - Index
Col3 - Latitude
Col4 - Longitude
Table 2: Brand Locations
Col1 - Brand
Col2 - Latitude
Col3 - Longitude
I'd like to merge the datasets so that the result shows:
Col1- Brand
Col2- Type (Index vs Location)
Col3- Index
Col4- Latitude
Col5- Longitude
But when I try to merge the datasets, or append them, I end up with two columns for Latitude and 2 columsn for Longitude. Any advise here would be greatly appreciated.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
78 | |
76 | |
53 | |
37 | |
31 |
User | Count |
---|---|
101 | |
56 | |
51 | |
45 | |
40 |