Forum Discussion
How to return a specific field for a record
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 |
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
2 Replies
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
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
