Searching IDs in two tables with duplicate values

[cmalopez]

Hello.

I have two tables with single colums each one with different IDs.

Table1

ID1 ID2 Unidades
AAA 102 2
ABA 101 2
AAA 101 1
ACC 121 1
AAA 102 1
AAA 101 1

Table2

ID1 ID2 Unidades
AAA 101 2
AAA 101 2
AAA 101 1

I woud like to add a column to Table1 what shows if there is a row in Table2 with same combination of ID1, ID2 and Unidades but I need that when a match is found that row in Table2 cannot be used for further matchs.

For my example Table1 should be:

ID1 ID2 Unidades Match
AAA 102 2 False
ABA 101 2 False
AAA 101 1 True
ACC 121 1 False
AAA 102 1 False
AAA 101 1 False (Table2 row has been used for another match)

Thank you for your help.

[Nathaniel_C]

Hi @cmalopez ,

Is there meant to be a duplicate row in Table 2?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[cmalopez]

There will be sure duplicates in both tables, so I need to pair rows in both tables one-by-one and avoid that a paired row can be used in another search. I hope this clarifies my question a bit.

Thank you so much. 

[Nathaniel_C]

Hi @cmalopez ,

So how does PowerBI "know" to pick one row over the other to do the matching? I don't believe this can be done. Any one else?

I think you need to think about what you are trying to do here? This strikes me more as a programming problem, then data. In programming it might be workable with inner and outer loops.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel