Reply
Frequent Visitor
Posts: 13
Registered: ‎05-10-2017
Accepted Solution

Lookup one field, return multiple matches

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)
001Pro
001Live
002Pro
003Pro
004Live
005Live
006Live
006Pro
007Pro

 

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
001Complete
002Live
003Live
004Pro
005Pro
006Complete
007Live

 

Your help is much appreciated!

 

Thanks


Accepted Solutions
Highlighted
Moderator
Posts: 8,612
Registered: ‎03-10-2016

Re: Lookup one field, return multiple matches

[ Edited ]

Hi @rcalvo,

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

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

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

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"
4.PNG

Thanks,
Lydia Zhang

View solution in original post


All Replies
Highlighted
Moderator
Posts: 8,612
Registered: ‎03-10-2016

Re: Lookup one field, return multiple matches

[ Edited ]

Hi @rcalvo,

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

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

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

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"
4.PNG

Thanks,
Lydia Zhang