Create new column based on multiple conditions from a related table

[cretak]

Hi all,

I am currenlty learing PowerBI and also attending training classes. Therefore, coming across more and more use cases. One of which I struggle right now on how to solve it. I hope someone can provide some help with:

I have two tables. A project table and a project member table. They are connected via the project id. Would like to have the name of the project manager in the project table as a new column. 

Project Table:

project id	name
1	Implementation
2	Marketing

Project Member

project id	name	role	isLead
1	John	Scrum Lead	No
1	Melissa	Project Manager	Yes
1	Sam	Project Manager	No
2	Jeff	Project Manager	Yes

Expected Outcome

project id	name	project manager
1	Implementation	Melissa
2	Marketing	Jeff

Does anyone know how to solve this? Any help is highly appreciated!

Thanks Oli

[cretak]

Hi @dufoq3 ,

many thanks! Appreciate your help! It worked like a charm

[dufoq3]

You're welcome. Enjoy 😉

[dufoq3 · Accepted solution]

Hi @cretak , add this as Custom Column in Project Table:

Table.SelectRows(#"Project Member", (x)=> x[isLead] = "Yes" and x[project id] = [project id]){0}?[name]?