Resource assignment report on Planner and Planner Premium

I was recently asked how to report on resources in Planner Premium. And this got me thinking, could I do reporting on both planner (only cards and no hours/capacity) and Premium in one go?

Turns out you can, but… it takes some work to get there. Let me show you what to do, and at the end I’ll tell you where to get the template file.

Two different sources, one app?

Microsoft consolidated its Planner and scheduling solutions into one brand/name called Planner.

But this consolidation is front-end for now, meaning that the 3 applications (to do was a part of this as well) still have their original sources. That might change in the future, so keep an eye out for updates on this topic, I’ll probably do a follow-up article to rework the file.

Let’s focus on reporting on planner and Premium (for the sake of speed, I’ll refer to “Planner with Premium features” simply as Premium).

Premium has its data stored in the Dataverse, and Planner data is stored in a special folder in Microsoft 365 Group mailboxes.

Where to start?

There’s a free template that contains all relevant tables and fields for Premium. The thing is, its a bit old.

So, in order for us to use the file, we will do some cleaning.

For Planner, I did some searching and found a very skilled YouTuber called Christine Payton. On her channel she describes building a report on Planner & building the report on all plans perfectly. So we will use that in our report as well.

First though, Premium report clean-up!

Cleaning the Project Accelerator report

Here are four things we will do to make the report suitable for our resource focussed reporting needs.

• Select the correct environment
• Remove unneeded tables
• Remove filters on pages
• Remove unneeded pages

Select the correct environment

Planner Premium works on Dataverse. Although you could work from the default environment I would highly urge you not to. The Planner solution works best in a contained specific Dataverse enabled model driven Power App environment.

Selecting the environment is easy, and described here.
When opening the report template you will be prompted to provide that URL.

Removing unneeded tables

4 years ago we had a Roadmap feature along side the, then called Project for the Web, Planner structure.

This scenario has changed, and now we have portfolio’s, still the same thing, not much has changed but the connection. When you refresh the Power BI tables you will get this error message on the first roadmap table:

Expression.Error: The key didn’t match any rows in the table.
Details:
Key=
EntitySetName=msdyn_roadmaps
Table=[Table]

For our resource scenario we won’t need these tables either. In Power BI, you can transform the data, and remove these tables related to the roadmap feature:

• Roadmap
• Roadmap Row
• Roadmap Item
• Roadmap Item Link
• Roadmap Key Dates

You could of course remove the whole roadmap section in one go as well. The legacy data shows a bit in the Power Query Editor, Dataverse used to be called Common Data Service, Roadmap is Portfolio and Project for the web is now called Planner Premium.

— update: I wrote an article on how to get the portfolio in the report here —

Removing unneeded pages

We won’t need all the pages in the report when focussing on the resource assignments. For this report, we can remove the pages related to the roadmap feature described earlier. And we can either hide or remove more pages as well. I’m keeping these pages, and removing the rest:

• PORTFOLIO DASHBOARD
• PORTFOLIO TIMELINE
• PORTFOLIO MILESTONES
• RESOURCE DASHBOARD
• RESOURCE ASSIGNMENTS
• RESOURCE ALLOCATION
• TASK OVERVIEW
• PROJECT TIMELINE
• MY WORK

There are also a couple of tooltip pages that I’ll keep.

Removing any filters

While cleaning up the report, I noticed filters that were set to data not precent in the sources. You can find these filters here:

• On PORTFOLIO DASHBOARD the “Effort by Project” and “Projects by Project Manager” both have a top 10 filter. If your dataset is very large you can keep it. But I chose to remove it.
• On PORTFOLIO TIMELINE both slicers have a removed field. Roadmap Name and Owner Name don’t exist anymore.
• On RESOURCE DASHBOARD the visuals have a top 10 filter on as well. I chose to remove them.

Attaching the Planner data

Now that the report looks good and actually refreshes we should add the Planner information.

Planner is a bit different from Premium though. For our purpose, mainly because there is no capacity data. Luckily, we do have a start date and a due date that we can use as an end date. This will help us create Gantt chart visuals.

Apart from the Gantt we are limited to the count of tasks by X. Where X could be Projects, labels (probably) priority, etc.

Because Christine Payton already did a great job in her multi-part series on planner reporting I won’t rehash her work here, I would encourage you to view the videos yourself instead.

Gathering the data from Planner

I will share 3 key lessons with you from the videos:

• Planner data accross plans cannot be directly accessed for reporting, instead you need a Power Automatr Flow to gather the information and store it centrally (as a JSON file). In my Power BI file I made sure to include a parameter referencing this JSON file. When building a template file, this parameter shows up for you to fill in.
• Only plans that you have access to show up in this flow. Make sure the flow runs under a service account. Then, make sure that all plans you’d like to view in the report are shared with that service account.
• Some features from the plan are difficult to get, as an example: priority isn’t available straight away, luckely Christine also noticed this and found the solution in her video’s.

Adding the Planner data to Power BI

The file I created in the previous step can now be used as a source in our Power BI file. There are 4 tables of interest to our reporting needs:

Tasks

Our key value coming from any plan, also our anchor for the other tables.

Grabbing the information can be done by using this M query (make sure to replace <<Your SharePoint JSON file here>> with your own SharePoint file URL):

let
    Source = Json.Document(Web.Contents("<<Your SharePoint JSON file here>>")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"groupId", "groupName", "planId", "planName", "taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "assignees", "subtasks", "buckets", "Priority"}, {"groupId", "groupName", "planId", "planName", "taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "assignees", "subtasks", "buckets", "Priority"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"taskId", type text}, {"taskName", type text}, {"taskDescription", type text}, {"taskProgress", Int64.Type}, {"taskStart", type datetime}, {"taskDue", type datetime}, {"taskComplete", type datetime}, {"assignees", type any}, {"subtasks", type any}, {"buckets", type any}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"groupId", "groupName", "planId", "planName", "taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "buckets", "Priority"}),
    #"Expanded buckets" = Table.ExpandListColumn(#"Removed Other Columns", "buckets"),
    #"Expanded buckets1" = Table.ExpandRecordColumn(#"Expanded buckets", "buckets", {"bucketName"}, {"bucketName"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded buckets1",{{"taskComplete", type date}, {"taskDue", type date}, {"taskStart", type date}}),
    #"Added Progress" = Table.AddColumn(#"Changed Type1", "Progress", each if [taskProgress] = 0 then "Not Started" else if [taskProgress] = 50 then "In Progress" else if [taskProgress] = 100 then "Completed" else "Not Planner"),
    #"Added Overdue?" = Table.AddColumn(#"Added Progress", "Overdue?", each if([taskDue]<> null and [Progress] <> "Completed" and [taskDue]<DateTime.Date(DateTime.LocalNow()))then "true" else "false"),
    #"Added Conditional Column" = Table.AddColumn(#"Added Overdue?", "Priority label", each if [Priority] = 1 then "Urgent" else if [Priority] = 3 then "Important" else if [Priority] = 5 then "Medium" else if [Priority] = 9 then "Low" else "N/A"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Progress", type text}, {"Overdue?", type text}, {"Priority label", type text}})
in
    #"Changed Type2"

Subtasks

Subtasks are the checkbox items you can create in Planner. A very cool feature, that requires some additional changes before they show up the way we want them to:

let
    Source = Json.Document(Web.Contents("<<Your SharePoint JSON file here>>")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"groupId", "groupName", "planId", "planName", "taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "assignees", "subtasks", "buckets"}, {"groupId", "groupName", "planId", "planName", "taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "assignees", "subtasks", "buckets"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"taskId", type text}, {"taskName", type text}, {"taskDescription", type text}, {"taskProgress", Int64.Type}, {"taskStart", type datetime}, {"taskDue", type datetime}, {"taskComplete", type datetime}, {"assignees", type any}, {"subtasks", type any}, {"buckets", type any}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"groupId", "groupName", "planId", "planName", "taskId", "subtasks"}),
    #"Expanded subtasks" = Table.ExpandListColumn(#"Removed Other Columns", "subtasks"),
    #"Expanded subtasks1" = Table.ExpandRecordColumn(#"Expanded subtasks", "subtasks", {"id", "value"}, {"id", "value"}),
    #"Expanded value" = Table.ExpandRecordColumn(#"Expanded subtasks1", "value", {"isChecked", "title"}, {"isChecked", "title"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded value", each ([id] <> null))
in
    #"Filtered Rows"

Assignments

Assignments,,, well yes the basis of an assignment at least. Please remember that this isn’t a real assignment like we have in Planner Premium. This assignment contains a name, a start date and a end date. But no work, or percentage complete for the assignment itself. Here’s the M-code:

let
    Source = Json.Document(Web.Contents("<<Your SharePoint JSON file here>>")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"groupId", "groupName", "planId", "planName", "taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "assignees", "subtasks", "buckets"}, {"groupId", "groupName", "planId", "planName", "taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "assignees", "subtasks", "buckets"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"taskId", type text}, {"taskName", type text}, {"taskDescription", type text}, {"taskProgress", Int64.Type}, {"taskStart", type datetime}, {"taskDue", type datetime}, {"taskComplete", type datetime}, {"assignees", type any}, {"subtasks", type any}, {"buckets", type any}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"taskId", "assignees"}),
    #"Expanded assignees" = Table.ExpandListColumn(#"Removed Other Columns", "assignees"),
    #"Expanded assignees1" = Table.ExpandRecordColumn(#"Expanded assignees", "assignees", {"assigneeId", "assigneeName"}, {"assigneeId", "assigneeName"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded assignees1", each ([assigneeId] <> null))
in
    #"Filtered Rows"

Flags (optional)

Flags are an important item within Planner, making it possible for you to add additional data to a task and sort/filter on that value. I won’t use them in my report much, though, because the flags are different on each plan. Add this table using this M-code:

let
    Source = Json.Document(Web.Contents("<<Your SharePoint JSON file here>>")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "assignees", "subtasks", "buckets"}, {"taskId", "taskName", "taskDescription", "taskProgress", "taskStart", "taskDue", "taskComplete", "assignees", "subtasks", "buckets"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"taskId", type text}, {"taskName", type text}, {"taskDescription", type text}, {"taskProgress", Int64.Type}, {"taskStart", type datetime}, {"taskDue", type datetime}, {"taskComplete", type datetime}, {"assignees", type any}, {"subtasks", type any}, {"buckets", type any}})
in
    #"Changed Type"

With these 4 tables included in the model, the Power Query editor looks something like this:

Setting relationships in the model

Once you have all the tables from Planner, it’s time to attach the relationships to the tables. In the end, the relations should look something like this:

For Premium the relations look like this:

For Planner the relations look like this, and are all focussed on the Task ID:

The results

Here is a page by page description of the report and its content.