Planner data into Power BI
Enough customers of mine have asked to visualize data from Planner in Power BI that I think it’s time to make a simple solution for people to use. No more hassle, no more setting up ways to collect the data first and then ingest into Power BI, but rather a Power BI Template, ready for anyone to use on their plans!
First we’ll go through how to actually use the report and if you scroll down you’ll get the instructions on how to set it up!
Using the Template
The report currently only holds one page but feel free to build it out any way you like. You have the data!
On the upper left corner, where we typically look first, you’ll find total number of tasks in the plan as well as how many of them are currently open. You’ll also see how many are in different progresses, as in New, In progress or Done. This might give you an idea of how your plan is moving along! Note the colors there. They’re important as they return on the Status filter as well as in the list of tasks to the right.
This simple area chart will tell you how many tasks were created or closed over time. You can use it to get a sense of how active your plan is. Are people adding and completing tasks daily, weekly or was it 3 weeks since anything was actually added or closed to your plan?
Next you’ll find filtering options. Status we already saw, so white is new, blue in progress and green for done. Priority looked more interesting with filled bars and an alert signal than just a number, I thought. In Planner, you’ll have 4 priorities out of the box and the alarm lamp indicates the Urgent one.
Moving on you’ll se a list of tasks with a status indicator, the priority and if there are checklist items on the task. If so, you’ll also see number of completed checklist items out of the total number as well as how many percent completed checklist items there is. On the right you’ll see the checklist items separate and you can select any task to focus only on the checklist items on that task. Interestingly, I’ve gotten the nails and hit them hard with the top of my hammer but I have get to get the hammer.. Hm.
Moving on to the bottom half of the report, on the left side there are 2 graphs and a Metric Selection. The idea is that we always display the Bucket and Categories, but you decide if you want to see all the open tasks, the new ones etc.
Sure this could’ve been done by simply filtering on status too, but I also wanted to show you this possibility and perhaps a small grain of “Aha, wait I know what to use this feature for in one of my reports” would set it roots in you 😉
Final visual is a Gantt chart that depends on your tasks start and due dates. It might give you a better understanding of where you are with your ongoing tasks.
This is depending on a calculated field called “Duration Days”. To understand why some tasks appear in the Gantt chart and others don’t, you need to understand the logic here. First it creates “StartDate” that is either the set start date on the task or the tasks created date. If you have not set a start date, it’ll be the created date. Then it creates the “endDate”. If the CompletedDate is NOT null, i.e. you do have a completion date and the task is done, we use that date as the end of the task, otherwise we use the due date. If you don’t want the logic this way, for example you always want the duedate, this is the code you need to adjust. Finally it checks if the end date is null, i.e. both completiondate and due dates are null, in which case we cannot calculate the duration (I guess we could’ve set it to todays date but it would just make a messy chart I think. If it has a value it calculates the value between start and end, in days.
Setting it up for a new plan
Go to GitHub and download the PBIT file first. Here’s a direct link!
Make sure you have Power BI Desktop installed on your computer.
Double click on the PBIT file that you saved. This popup will appear in an otherwise blank Power BI report:
Head over to you planner and make sure you open it in a browser, i.e. not in Teams, so that you can see the URL in the address bar. Copy the PlanID (which is not a GUID, I know.. Mistakes were made and I’ll correct it later. Perhaps. It still works!)
After pasting the PlanID into Power BI you click “Load” and it’ll start to download data from the Graph API:
The first time you set up a new plan, you have to log in, in which case you should use “Organizational account” and sign in using your normal Entra ID account.
If this window appear, simply set it to Organizational or select the ignore option.
When you’re done, save the report with any name you want, all though I’d recommend adding the name of the Plan somewhere in there. Publish the plan to your desired workspace in Power BI and set up scheduled refreshes so you don’t have to worry about that, then you can for example add the report back into Teams or embed in SharePoint or a PowerPoint.
If you want to set up another plan, simply redo the steps but add the other plans Plan ID. You can publish the reports side by side in the same workspace and, for example, publish both in an App. In this case you really should name the reports with the plan names though.
Limitation
The thing this is missing is the assignments and I’m afraid that cannot be done using the normal user account as credentials. The reason is that you’re only allowed to fetch the user ID from the plan and that’s a GUID, not really useful for humans. It needs to be mapped to the rest of the user accounts attributes from Entra ID to make sense and that data cannot be accessed using a normal user account from the Graph API at the moment. You can still get that if you use a Service Principal for example, so if you have important production reports I don’t recommend using this template, rather fetch the data in a proper backend first. This report is however really easy to get started with and should you feel the need to expand on it, feel free to do so! If you feel limited on what can be done with Power Query and your normal user account than that’s also great! You now have a really good business case for setting something up in Fabric for example. Good luck!
Cheers!
About The Author
