How to return a specific field for a record

[Anonymous]

Hi, I have connected PowerBI to Microsoft Project Online and have a question on how to return the relevant project and programme manager for a project.

All projects, programmes and portfolios are contained in the projects table. Each row is a project, programme or portfolio. Each row has a 'type' field that identifies which of these the row is. Each row also has a 'manager' field, a 'programme' field that identifies what programme the row belongs to and a  'portfolio' field that identifies what portfolio the row beleongs to. So an example table would be:

Portfolio	Programme	Project	Type	Manager
Mars Colonisation Portfolio	Spaceship programme	Wooden Mustard	Project	Brent Bell
Mars Colonisation Portfolio	Spaceship programme	Iron Spaniel	Project	Brent Bell
Mars Colonisation Portfolio	Spaceship programme	Spaceship programme	Programme	Darren Marsh
Mars Colonisation Portfolio	NOT PART OF A PROGRAMME	Mars Colonisation ortfolio	Portfolio	Julius Floyd
Mars Colonisation Portfolio	Establish base programme	Gray Titanium	Project	Jobe Ray
Mars Colonisation Portfolio	Establish base programme	Platinum Beagle	Project	Jobe Ray
Mars Colonisation Portfolio	Establish base programme	Establish base programme	Programme	Lorenzo Colley

I wish to return the relevant programme and portfolio manager for each record, as follows. How would i do this?

Unfortunately I cannot separate the projects, programmes and portfolios into separate tables as the data strcuture is managed by Microsoft Project Online...

Portfolio	Programme	Project	Type	Manager	PROGRAMME MGR	PORTFOLIO MGR
Mars Colonisation Portfolio	Spaceship programme	Wooden Mustard	Project	Brent Bell	Darren Marsh	Julius Floyd
Mars Colonisation Portfolio	Spaceship programme	Iron Spaniel	Project	Brent Bell	Darren Marsh	Julius Floyd
Mars Colonisation Portfolio	Spaceship programme	Spaceship programme	Programme	Darren Marsh	Darren Marsh	Julius Floyd
Mars Colonisation Portfolio	NOT PART OF A PROGRAMME	Mars Colonisation ortfolio	Portfolio	Julius Floyd	-	Julius Floyd
Mars Colonisation Portfolio	Establish base programme	Gray Titanium	Project	Jobe Ray	Lorenzo Colley	Julius Floyd
Mars Colonisation Portfolio	Establish base programme	Platinum Beagle	Project	Jobe Ray	Lorenzo Colley	Julius Floyd
Mars Colonisation Portfolio	Establish base programme	Establish base programme	Programme	Lorenzo Colley	Lorenzo Colley	Julius Floyd

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

Create two column as below:

PROGRAMME MGR = CALCULATE(MAX('Table'[Manager]),FILTER('Table','Table'[Portfolio]=EARLIER('Table'[Portfolio])&&'Table'[Programme]=EARLIER('Table'[Programme])&&'Table'[Type]="Programme"))

PORTFOLIO MGR = CALCULATE(MAX('Table'[Manager]),FILTER('Table','Table'[Portfolio]=EARLIER('Table'[Portfolio])&&'Table'[Type]="Portfolio"))

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[manikumar34]

@Anonymous , 

You can create a conditional column on Power query to do so. 

#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Type] = "Portfolio" then [Manager] else null),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Portfoli MGR"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Type] = "Programme" then [Manager] else null),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "Programme MGR"}})

If you want to fill the oother rows which are empty, on else part you can mention the Value to be filled. 

Regards, 

Manikumar