Update the #Projectfortheweb #PowerBI template to include new data for labels and check lists #ProjectManagement #WorkManagement #TaskManagement #MicrosoftProject

As you are probably aware, Microsoft released two new features for Project for the web:

Labels: https://pwmather.wordpress.com/2022/04/06/task-labels-now-available-in-projectfortheweb-projectmanagement-workmanagement-taskmanagement-microsoft365/ 

Checklists: https://pwmather.wordpress.com/2022/01/25/projectfortheweb-task-checklist-feature-microsoftproject-msproject-workmanagement-taskmanagement-microsoft365/ 

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.

New queries

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:

queries

Then click Paste to create a copy of that query:

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:

checklist

After:

query

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:

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:

query

After:

query

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:

query

After:

query

Click Done and you should see task label data in the table. Delete the selected “Dataverse URL..” queries:

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:

new query

You should see the following:

new blank queries

Rename the 3 blank queries as seen below:

renamed

Select the “Project Task Checklists” query and click “Advanced Editor” and make the following changes:

Before:

query

After:

query

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:

steps

Create a new column in the “Project Labels” dataset called “Label”:

new column

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:

checklists to tasks

Join “Project Labels” to “Project Label to Tasks”:

labels to task labels

Join “Project Label to Tasks” to “Project Tasks”:

task labels to 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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: