How to join 2 table that has many-to-many relationship

[CKK]

Hi all,

My question is below. I have two tables connected to each other with "Order" column and many-to many relationship. I want to get a new table shown as below. Is there any way to have it? I am a newbie so, if it is a dumb question, sorry about that.

Thanks in advance.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

GENERATE function (DAX) - DAX | Microsoft Learn

Expected result table = 
GENERATE('Table 1', SUMMARIZE('Table 2', 'Table 2'[Operation]))

[talespin]

Hi @CKK 

No need to be sorry, create three tables instead. Avoid Many to Many relationships.

Table1 unique parts

Table 2 unique operations

Table 3 Orders

Join Table1 and Table2 to Table3 using One-Many relationship.

You do not need to create a combined table, what is it you want to do with combined table?

Problems with combined table

- Additional storage.

- Slow refresh time.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

GENERATE function (DAX) - DAX | Microsoft Learn

Expected result table = 
GENERATE('Table 1', SUMMARIZE('Table 2', 'Table 2'[Operation]))

[Anonymous · Accepted solution]

Hi @CKK 

You can use Merge to achieve this effect.

Delete the Order column

Is this the result you expect?

For more details, you can read related document link: Merge queries overview - Power Query | Microsoft Learn

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.