How to get a single and multiple match between two tables

[Danielecc]

Hi everyone,

I want to get a single/multiple match between two tables (Table 1 is a calculate table), and the result expected is a New calculated table. Tables are not related.

I need to get if "Table 1" match with "Table 2" . Table 2 could have until 5 mix, but actually the maximun mix I have are three. 

I will paste in text mode the data:

Table 1:

USERS	MIX
Employee1	Caso1
Employee1	Caso2
Employee1	Caso6
Employee1	Caso4
Employee1	Caso9
Employee1	Caso10
Employee1	Caso11
Employee2	Caso5
Employee2	Caso2
Employee2	Caso6
Employee2	Caso4
Employee2	Caso7
Employee2	Caso3
Employee3	Caso1
Employee3	Caso6
Employee3	Caso3
Employee3	Caso8
Employee3	Caso9
Employee3	Caso10
Employee4	Caso3
Employee4	Caso4
Employee4	Caso7
Employee4	Caso8
Employee4	Caso10
Employee4	Caso11

Table 2:

IMPACTO	Mix1	Mix2	Mix3	Mix4	Mix5
Critical1	Caso1
Critical2	Caso5
Critical3	Caso2
Critical4	Caso6	Caso3
Critical5	Caso6	Caso4
Critical6	Caso7	Caso3
Critical7	Caso8	Caso9	Caso10
Critical8	Caso8	Caso10	Caso11
Critical9	Caso9	Caso10	Caso11

Thanks a lot for your help and best regards...

[Danielecc]

Hi Miguel, and thanks for your answer.

My "table 1" is a calculate table, then I can't do that. BUT I solve the problem doing a change of distribution on "Table 2".

I will accept as solution your answer cause must work with "no calculate tables.

Best regards

[Ridzwan]

Hi Danielecc,

Do you mind explaining how you achieved this?

[MFelix · Accepted solution]

Hi @Danielecc ,

You can do this on Power Query. Do the following steps:

• Add new blank query that refers to table 1
• Add a new step and refer to table 2
• Select the Impact column and unpivot other columns
• Filter the blank values
• Remove the attribute column
• Do a merger of the two table referincing the Source step and the Remove columns
• Do the expand of the table by the max of the IMPACT.
• Remove duplicates

See result below and in attach PBIX file