Forum Discussion
Add 2nd table list to row on 1st table based on common key then expand
I have 2 tables with the following data:
Table 1 - Issues table
| Issue | Project |
| 100 | 10 |
| 600 | 5 |
| 300 | 12 |
| 400 | 8 |
| 500 | 10 |
| 610 | 12 |
| 301 | 5 |
| 302 | 5 |
Table 2 - Holds relationship of product areas to a project
| Project | Product Area |
| 12 | pa_50 |
| 12 | pa_52 |
| 12 | pa_45 |
| 10 | pa_16 |
| 10 | pa_12 |
| 8 | pa_100 |
| 5 | pa_11 |
| 5 | pa_55 |
I want to get to: Table 1 being
| Issue | Project | Product Area |
| 100 | 10 | pa_16 |
| 100 | 10 | pa_12 |
| 200 | 5 | pa_11 |
| 200 | 5 | pa_12 |
| 300 | 12 | pa_50 |
| 300 | 12 | pa_52 |
| 300 | 12 | pa_45 |
| 400 | 8 | pa_100 |
| 500 | 10 | pa_16 |
| 500 | 10 | pa_12 |
| 600 | 12 | pa_50 |
| 600 | 12 | pa_52 |
| 600 | 12 | pa_45 |
| 301 | 5 | pa_11 |
| 301 | 5 | pa_12 |
The reasons for this is I want to filter on Product Area in the final report. I will handle the duplicates with measures.
I can't get table 1 to join with table 2 on Project. It says I can't join m & m.
I think I need to move table 2 rows in as a comma separted list on each row of table 1 based on the project.
Then I need to expand each list getting the resulting rows. How does one do this in the UI so the m-code is correct.
Thanks!!!
I have added the two tables to a pbix file
And gone for a merge.
And there doesnt appear to be any problems for me either. Are you trying to join them in the actual data model?
8 Replies
camargos88Community Champion
Hi Anonymous ,
The merge function in Power Query doesnt work ? It will replicate the repeated values, like you said.
- camargos88
Anonymous ,
If you are using Power Query, you don't get this error, it will replicate with n number of duplicated rows.
Can you post a print how you are getting this error ?
- v-eachen-msft
Community Support
Hi Anonymous ,
It doesn't look like an error which is in power query, I could merge tables properly. What did you do to cause this error?
- DebbieE
I have added the two tables to a pbix file
And gone for a merge.
And there doesnt appear to be any problems for me either. Are you trying to join them in the actual data model?
