Here at Definitive Logic we practice Kanban for agile software development. One of the biggest reasons we use Kanban is it inherently provides a visual for our colleagues and customers via the Kanban Board. Furthermore, following Kanban allows us to collect data to produce visual metrics, focus on improvements, and report those improvements to our customers.

In many cases our team uses Atlassian JIRA, but on a recent project this was not an option since a big requirement was not to incur additional licensing costs. However, we did have the Microsoft 365 tool known as Planner along with the Power Platform (Power Automate, PowerApps, and Power BI). This posed a challenge for us since getting the data we needed to build Kanban Metrics was not easily available. We are happy to report we did find a solution, so in this series of articles, we will share how to implement Kanban Metrics on Microsoft 365 and how to overcome some of the limitations. In our first article, we will cover how we built the data collection necessary for Kanban Metrics and in our second article we will cover how we produced those metrics in Power BI.

Background

When we first looked to replicate a Kanban board with just Microsoft 365 tools, we quickly gravitated to Microsoft Planner. Released three years ago, Microsoft Planner is a planning/task management tool that is provided with many of the Microsoft 365 (formerly Office 365) licenses. Planner, at its core, consists of Buckets and Tasks where Buckets are ways to logically group tasks. Figure 1 provides a snapshot of planner with 3 Buckets labeled Planning, Execution, and Done.

Figure 1 – Example of Buckets labeled Planning, Execution, and Done in Microsoft Planner

Each task within Planner has a set of fields including who is assigned the task, when the task was started, and when the task is due. Figure 2 provides an example of the Planner form.

Figure 2 – Example of Task form in Microsoft Planner

While Planner was easy to setup for Kanban in the beginning, we quickly realized a major feature missing from Planner. There was no way to track version history! In other words, the interface did not provide a way to see a history of changes to a task. If we wanted to track the time it took a task to move between different buckets or identify bottlenecks in our development process, we were out of luck.

And it seemed like we were not alone in requiring this capability; task history is a requested feature from the Microsoft 365 community that has yet to be implemented.

The Solution

Consequently, we had to get creative. We started to investigate the existing Planner Application Programming Interface and homed in on the ETag field that was associated with each task. After doing some testing, we noticed that every time a task was updated the ETag would change. Furthermore, if we combined the ETag field with the task’s ID field (a GUID), we could presume we have a unique version of a task.

{
“@odata.etag”: “W/\”JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBARCc=\””,
 “planId”: “S3cf-DF1q0KUhP67zHf5imUAB4yo”,
“bucketId”: “Cb6nr1ZV-EuFkcFUUgsQBGUAMkV8”,
   “title”: “Build Widget”,
“orderHint”: “8586124553598571709P1”,
“assigneePriority”: “”,
“percentComplete”: 0,
“startDateTime”: null,
“createdDateTime”: “2020-05-10T22:59:45.636045Z”,
“dueDateTime”: null,
   “hasDescription”: false,
“previewType”: “automatic”,
  “completedDateTime”: null,
  “completedBy”: null,
“referenceCount”: 0,
“checklistItemCount”: 0,
“activeChecklistItemCount”: 0,
“conversationThreadId”: null,
    “id”: “7PiXjb4xR0-CMycbnZ7ztWUABRuX”,
 “createdBy”: {
 “user”: {
“displayName”: null,
“id”: “c3bf0d41-e924-4c16-bca4-15a1dfbb7c2d”
}
},
  “appliedCategories”: {},
“assignments”: {},
   “_assignments”: []
}

Figure 3 – Example JSON for a task in Planner as provided through the Planner API

With that key discovery we could now build out a way to collect version history of tasks.  At a high level the version history would be collected by doing the following:

  • Create a SharePoint list with two fields, Title and JSON.
    1. Title would represent a concatenation of ETag and ID for a task. The Title field would be indexed.
    2. JSON would store a copy of the task’s JSON representation from the Planner API.
  • Create a Power Automate flow and run it at least hourly.
    1. We know that we may miss multiple changes within an hour, but that was a shortcoming with low risk given our processes.
  • Call the Planner API’s List Tasks to get all the tasks from the Kanban Board.
  • For each task get the ETag and ID and combine into a concatenated key.
  • Query the SharePoint list where the Title field equals the concatenated key.
  • If no results are returned, then we have a new version of a task and we need to save this version in SharePoint.

Thankfully, our experienced team built out the SharePoint list and Power Automate flow within a couple of hours.  Figure 3 is a screenshot of our flow.

As we rely on the Microsoft community for ideas and solutions, we would like to share our work, so the SharePoint list template and Flow template is available on our GitHub site at this link.

Figure 4 – Power Automate Flow collecting task versions from Planner and saving to SharePoint

In our next blog article, we will go over how to take the task versions now stored in SharePoint and build out Kanban Metrics with Power BI.

We hope that you find this article helpful, and if you are interested in building out Kanban with Microsoft 365 in your organization or have questions, please contact us at info@definitivelogic.com.