Lookup one field, return multiple matches

[rcalvo]

Hi All,

I have two tables, one (Table A) with unique ID and another one (Table B) with non unique values that refere to Table A.

Table A
ID
001
002
003
004
005
006
007

Table B
ID(Table A)
001	Pro
001	Live
002	Pro
003	Pro
004	Live
005	Live
006	Live
006	Pro
007	Pro

Here is what I am trying to achieve: I want to know which ID's  from Table A are missing either "Pro" or "Live" in Table B. If They are complete then "Complete"

Table A
ID	Missing
001	Complete
002	Live
003	Live
004	Pro
005	Pro
006	Complete
007	Live

Your help is much appreciated!

Thanks

[v-yuezhe-msft · Accepted solution]

Hi @rcalvo,

In Query Editor, firstly click on your Table A, then select “Merge Queries as New” to merge the two tables.

Secondly, add custom column named “Status” in your merged table.

= Text.Combine([NewColumn][Status],", ")

Thirdly, add a custom column named “Missing” in your merged table and you will get expected result.

= if [Status]="Pro, Live" or [Status] ="Live, Pro" then "Complete"
else if [Status] ="Pro" then "Live" else "Pro"

Thanks,
Lydia Zhang