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
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"),
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