Power Automate for Analytics and Reporting on Microsoft Planner

Screenshot of Testing planner at SAP Demonstration Digital Assets
Test Plan at SAP Americas Digital Demonstration Assets

MS Planner is a Kanban project management tool that integrates closed with Microsoft Teams and uses Azure Active Directory and Office 365 Groups for membership, identity and permission granting. I admire Nadella’s Microsoft immensely. Some of their offerings like Sway are quirky and lovable. Planner, their Kanban-interface project management tool is visually appealing and slick. But as this project proved definitively, it’s API leaves something to be desired.

SAP used Planner to track the 28 global business units served by Demonstration Digital Assets, a team of almost 500 staff and contractor professionals who develop sales and marketing video collateral to be used in SAP marketing efforts. There were about 1,000 videos in production or recently launched, and the manager of Digital Production wanted a way to track videos down a sequenced development path whose stages were identical across business units.

The 28 Plans

The legacy approach relied about end of week manual updates to a very large excel spreadsheet that integrated portfolio progress across the Planner Kanban, logged snags and changes in notes, and displayed over 60 attributes per video – to say nothing of joining viewing analytics statistics from the video serving platform, Kaltura. The legacy approach was to manually track the end-of-week state of the portfolio in excel, and use PowerPivot for analysis.

They wanted to Power BI for reporting and we had to migrate the data structure and monitor the portfolio of videos in Planner using SharePoint lists that held data updates from Planner via Power Automate. The only problem was the Planner API did not monitor for state changes from bucket to bucket (as the steps in the path were known). It only monitored the creation of a task, the assignment of a task to users, and the completion of a task.

Worse, because many tasks had more than one assignee, and assignee responsibility in a project management tool is, unsurprisingly rather important, there was no way to output a video-only representation of things: each row tracked was a video-assignee conjoint pair, on average 1.7 per video. So considerable overhead was spent in determining which rows had the privilege of being the primary mapped one to the video.

It’s one of those situations where as your formulating your requiremetns and your strategy, you’re wondering to yourself: the API can’t be that bad. And then you discover others have been grousing about your pain points since 2018, which in cloud time in unconscionably long.

C’mon, MSFT. You’re better than this.

Learn more in this sway.

View The Sway