About this lesson
One potential source of m:m (many to many) join issues in Power BI can be solved by combining multiple columns into a "composite key" in order to relate tables together. This module will dig into this issue, as well as how we solve the problem.
Download this lesson’s related exercise files. You can download the source data files from the top of your Lessons page.Solving many to many Joins via Composite Keys.pbix
95 KB Solving many to many Joins via Composite Keys - Completed.pbix
Solving m:m Joins via Composite Keys
Creating composite keys in order to link tables via one to many relationships.
When to use
Use when you need to consolidate two (or more) columns in order to create a column that can be used to create one to many joins.
An example of this type of relationship is where Account and Dept are in separate columns, but the account number shows up in multiple departments. To create a composite column:
- Edit your query
- Select the 2 columns (the primary column first)
- Go to Add Column --> Merge
- Choose to use a separator
- Rename the column
Hints & tips
- This technique can work well on its own, but may also require table de-aggregation as well (covered in the section on Solving m:m joins via De-Aggregation)
Lesson notes are only available for subscribers.