Add 2nd table list to row on 1st table based on common key then expand

[Anonymous]

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!!!

[DebbieE · Accepted solution]

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?

[Anonymous]

Been on vacation would have responded sooner. Thanks for your efforts. I retried and I was also successful. I have no idea why it gave me push back but now I'm able to move on. Again, thank you very much for your time. Well apprciated.

[v-eachen-msft]

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?

[Anonymous]

here the results of the merge I suggested. seems what you expect

except for the issue 200 that you have in the output is not present in the input tables 

PS

perahpas you use a old version of PBI?

[Anonymous]

[camargos88]

Hi @Anonymous ,

The merge function in Power Query doesnt work ? It will replicate the repeated values, like you said.

[Anonymous]

Ricardo,

I can't directly join. It's a many to many relationship.

"I can't get table 1 to join with table 2 on Project. It says I can't join m & m."

Thanks!

Steven

[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 ?