As you are probably aware, Microsoft released two new features for Project for the web:
These datasets are not in the default Power BI report template for Project for the web. In this blog post we will walkthrough the steps required to get this data into the default Power BI template found here: https://github.com/OfficeDev/Project-Power-BI-Templates/blob/master/Project%20for%20the%20Web/Microsoft%20Project%20for%20the%20Web%20Power%20BI%20Template.pbit You can also use these steps if you have a custom Power BI reports for Project for the web – just adjust the steps as required.
Open template in Power BI Desktop and connect this to your Dataverse environment. Click Transform Data in the Home ribbon to open Power Query. Expand the Queries folders to show the Project for the web > Staging Queries then right click on one for the queries and copy:
Then click Paste to create a copy of that query:
This is to create a new query – you can use the “New Source > More > Power Platform…” steps if you prefer. Rename the query, in the example, rename “Projects Staging (2)” to “Project Task Checklists Staging”. Open the new query using the “Advanced Editor” option and make the following changes:
Before:
After:
Click Done and you should see checklist data in the table.
Right click and copy the new Checklists query and paste to give another new query, repeat this to give 2 new queries:
Rename “Project Task Checklists Staging (2)” to “Project Labels Staging” and rename “Project Task Checklists Staging (3)” to “Project Label to Tasks Staging”. Select the “Project Labels Staging” query and click “Advanced Editor” and make the following changes:
Before:
After:
Click Done and you should see label data in the table. Select the “Project Label to Tasks Staging” query and click “Advanced Editor” and make the following changes:
Before:
After:
Click Done and you should see task label data in the table. Delete the selected “Dataverse URL..” queries:
We now have the staging tables added to the Power BI report template.
Add 3 new blank queries in the Project for the web folder using the “New Source > “Blank Query” option:
You should see the following:
Rename the 3 blank queries as seen below:
Select the “Project Task Checklists” query and click “Advanced Editor” and make the following changes:
Before:
After:
Click Done and you should see task checklist data in the table. Repeat the same steps for the “Project Labels” and the “Project Label to Tasks” queries with the following source values:
- Project Labels = #”Project Labels Staging”
- Project Label to Tasks = #”Project Label to Tasks Staging”
Transform the data in the newly added queries as needed, such as removing columns that are not needed in reporting and rename the columns:
Create a new column in the “Project Labels” dataset called “Label”:
This has the following formula:
if [Project Label Text] = null and [Label Color] = “Color0” then “Pink”
else if [Project Label Text] = null and [Label Color] = “Color1” then “Red”
else if [Project Label Text] = null and [Label Color] = “Color2” then “Yellow”
else if [Project Label Text] = null and [Label Color] = “Color3” then “Green”
else if [Project Label Text] = null and [Label Color] = “Color4” then “Blue”
else if [Project Label Text] = null and [Label Color] = “Color5” then “Purple”
else if [Project Label Text] = null and [Label Color] = “Color6” then “Bronze”
else if [Project Label Text] = null and [Label Color] = “Color7” then “Lime”
else if [Project Label Text] = null and [Label Color] = “Color8” then “Aqua”
else if [Project Label Text] = null and [Label Color] = “Color9” then “Gray”
else if [Project Label Text] = null and [Label Color] = “Color10” then “Silver”
else if [Project Label Text] = null and [Label Color] = “Color11” then “Brown”
else if [Project Label Text] = null and [Label Color] = “Color12” then “Cranberry”
else if [Project Label Text] = null and [Label Color] = “Color13” then “Orange”
else if [Project Label Text] = null and [Label Color] = “Color14” then “Peach”
else if [Project Label Text] = null and [Label Color] = “Color15” then “Marigold”
else if [Project Label Text] = null and [Label Color] = “Color16” then “Light green”
else if [Project Label Text] = null and [Label Color] = “Color17” then “Dark green”
else if [Project Label Text] = null and [Label Color] = “Color18” then “Teal”
else if [Project Label Text] = null and [Label Color] = “Color19” then “Light blue”
else if [Project Label Text] = null and [Label Color] = “Color20” then “Dark blue”
else if [Project Label Text] = null and [Label Color] = “Color21” then “Lavender”
else if [Project Label Text] = null and [Label Color] = “Color22” then “Plum”
else if [Project Label Text] = null and [Label Color] = “Color23” then “Light gray”
else if [Project Label Text] = null and [Label Color] = “Color24” then “Dark gray”
else [Project Label Text]
Where “Project Label Text” is the “msdyn_projectlabeltext” column and the “Label Color” is the “msdyn_colorindex_display” column – you might need to update the formula based on your column names.
Now the datasets are ready, click Close and Apply. Click on the Model tab and join the “Project Task Checklists” table to the “Project Tasks” table:
Join “Project Labels” to “Project Label to Tasks”:
Join “Project Label to Tasks” to “Project Tasks”:
You are now ready to build the new reports / update existing reports with Task checklist data and Task label data. I will publish this this new Power BI Template soon once I have added some default report pages for the new data.
Modern Work Management - Project and the Power Platform blog 