Context

I'm a fan of Microsoft Planner. It's easy to use, flexible, and the UI/UX is rock solid. The tie-in with O365 Groups and Teams is a strong selling point, as well.

Now, Planner isn't the perfect solution for every scenario (you probably wouldn't want to manage sprints with it) but it is very popular within many companies, large and small.

Definitions

Microsoft naming conventions don't always make things easy, so:

Integration Opportunities Abound

Planner can be used in many different ways, but at its core it's all about task management, which makes it a juicy target for automation. The Power Platform connector is pretty robust, and you can extend that even further by hitting the Planner API through Microsoft Graph.

However, any integration efforts quickly expose Planner's underlying data structure, which has some quirks.

Task Labels

Planner boards come with 25 labels to help you and your team classify the individual tasks on the board. Each label has a default name - its associated color - and that name can be customized to provide more meaning and context for the plan users. Cool feature!

Customizations, like label names, are contained at the individual plan level. For example, one team might use red for marking "Urgent" work and a different team may use it to classify something as "Top Secret."

The Challenge

When retrieving Planner task data, any label information comes back as an appliedCategories object:

It's not an array.

There are no name/value pairs.

It's just... booleans with no tie-back to the label names. What does category18 mean? Has it been customized? How can I tell?

Fortunately, you can get a bit of a "glossary" if you retrieve the overall plan data:

That's great, but how do I map those values? How do I bounce a name/value object against an object of booleans?

The Power Automate Way

Different platforms/languages will have their own approach to the challenges above. For Automate, it required some considerable thinking!

At the time this came up, I was working on a solution that iterated over many plans, so I needed a method that scaled. If you are working with data from just an individual plan, there are definitely some corners you could cut.

Building a Glossary Array

1.) When I got to the point in my automation where I was working with individual plans, I used the Get plan details action.

2.) Next, I used a Compose statement to craft my array. It took a lot of typing, but copy/paste made it less of an ordeal.

3.) Most teams that use labels within Planner also customize the label names - but it's not an absolute certainty. The Get plan details action only returns customized values. I wanted to be certain I covered both scenarios, so I used a coalesce expression within each node of the array.

Creating a Task Label Array

The steps above get us halfway there, but we still need to handle the other part of the mapping - the data from each individual task.

4.) Inside of a For each task loop, I started with a Parse JSON step, using the appliedCategories value as the content source.

5.) The schema needs to be created manually (unless you magically have a task with all 25 labels applied, to feed into the generator):

{ "type": "object", "properties": { "category1": { "type": "boolean" }, "category2": { "type": "boolean" }, "category3": { "type": "boolean" }, "category4": { "type": "boolean" }, "category5": { "type": "boolean" }, "category6": { "type": "boolean" }, "category7": { "type": "boolean" }, "category8": { "type": "boolean" }, "category9": { "type": "boolean" }, "category10": { "type": "boolean" }, "category11": { "type": "boolean" }, "category12": { "type": "boolean" }, "category13": { "type": "boolean" }, "category14": { "type": "boolean" }, "category15": { "type": "boolean" }, "category16": { "type": "boolean" }, "category17": { "type": "boolean" }, "category18": { "type": "boolean" }, "category19": { "type": "boolean" }, "category20": { "type": "boolean" }, "category21": { "type": "boolean" }, "category22": { "type": "boolean" }, "category23": { "type": "boolean" }, "category24": { "type": "boolean" }, "category25": { "type": "boolean" } } }

The Parse JSON step is important because it saves you having to write 25 expressions with null-handling logic in the next step.

6.) Next, I used a Compose statement (and a lot of copy/pasting) to create the initial array for the task labels. Note the key/value format matches the "glossary" we created previously.

7.) Then we filter the task label array to remove any nodes containing nulls. Booleans are tricky when filtering arrays in Automate, so switch to advanced mode and use an expression:

@equals(item()?['value'], true)

This yields an array like this:

[ { "key": "category14", "value": true }, { "key": "category18", "value": true }, { "key": "category19", "value": true }, { "key": "category23", "value": true } ]

Putting it All Together

8.) Now, you've got matching arrays - both sides of the equation! Where you take it from here may vary depending on your solution needs.

I needed each applied label as an individual record, so I looped through the newly-created task label array, filtering against the glossary array we created earlier with each iteration.

A couple people reached out to ask for more details on step #8 recently.

Here are the details on the For Each action and the Filter step just inside. You may need to zoom in or click the picture to see smaller details:

This last part may vary, depending on how you want the label data structured in your flow. I packed each value into an object and rolled those into an array variable using an Append action:

Here is the object, with each expression expanded. I include taskID since it's an effective way to relate child entities in Planner (labels, assignments, etc.) back to the parent:

{
  "labelTitle": first(body('filter_plan_labels_with_task_labels'))?['value'],
  "taskID": items('for_each_task')?['ID'],
  "labelID": int(
                substring(
                    first(body('filter_plan_labels_with_task_labels'))?['key'],
                    8
                )
             )
}

The final stage for this workflow gathers up the data collected for the various Planner entities and ships it off to a separate flow for processing and storage.

I hope that helps!

There is a pretty fun story to be told about this particular solution, but I will save that for another article - don't want to get off track here tonight.

Conclusion

This was an interesting puzzle to solve, and the only real drawback was that it involved a fair amount of repetition. But now that it's done, I've got a robust solution that can be scaled considerably.

Some of the upper-echelon Automate junkies might be able to squeeze a little more speed out of this method, but overall it is quite performant. Using Compose actions instead of variables any time you can get away with it definitely helps. Automate handles arrays and array filtering very quickly, too.