Forum Discussion
Join tables based on multiple columns
Hey,
in the query editor
- duplicate the columns that you want to use for the join
- mark the duplicated columns you want to use for the join, choose "Merge columns" from the context menu.
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
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
- duplicate the columns that you want to use for the join
- mark the duplicated columns you want to use for the join, choose "Merge columns" from the context menu.
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
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.
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"),
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.
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.
