PowerAutomate + Planner + List + PowerBI

[lbensen22]

Hi all, I am relatively new in this community and wanted to reach out about the problem I have.

I have a Flow that captures a task from MS Form > MS Planner. Now, I want to record all of those tasks into a SharePoint List and create a weekly report using PowerBI and send an email to a distribution list from an Excel file.

I know this is complicated but wanted to throw it out there in case anyone has experience in doing this,

Thanks in advance!

[PJS-Data-Admin]

@lbensen22 Hello! I know this is possible, but I need some clarification. What do you mean by "captures a task"? There are different ways to track tasks that are created in a Microsoft list. For example, I track every actionable item that occurs in all of my flows in one central MS List including tasks when they are created and completed. See this example:

You could do it this way, or use a flow that only tracks completed task, etc. Depends on the data you want to see regarding tasks.

Second, Power BI can get very tricky and expensive when it comes to accessing reports and sharing those reports. There is a cost to automate report exports from Power BI using Power Automate. So there are some possible alternatives depending on what you are trying to do and what report you are trying to create.

Let me know what your thoughts are, I can certainly help get these MS form tasks tracked in a SharePoint list.

[lbensen22]

Hi @PJS-Data-Admin, 

So here's my flow. 

- User fills out the Form

- I created a flow that will capture the Form details and put it as a Task/Card on the Planner

Now, I want all the Task/Card to be recorded, so I can track it. I am still not sure how to capture it but what I want to do is to create a report that can show the number of tasks, progress, number of people participating, etc.

My Planner is a Kanban board with 4 columns (Status) - New, Ongoing, Completed, Cancelled.

Each Task/Card will have owners, members, date creation, end date, priority. 

Each Task/Card will have changes on members, status, etc. 

I want to capture all of it in a report. 

Does it make sense?

[PJS-Data-Admin]

@lbensen22 Understood. This seems relatively standard. Does the MS Project dashboard not give you those metrics? See a sample below:

In order to track tasks, you can track them when they are created in your current flow. We would have to create a separate flow that tracks when the task is completed unless you use a "Do Until" item, but flows have a limit on how long they can run for. Let me know and I can create a sample for you.

[lbensen22]

@PJS-Data-Admin, I was thinking about it, and it might be better to capture it manually in an Excel and build a simple dashboard from there. We haven't rolled it out yet but I believe Planner has some metrics that can be generated.

I was also thinking that my used case is very specific and might not make sense to build a Flow for it. But here's my current flow. Where will you put the recording to SharePoint List?

[PJS-Data-Admin · Accepted solution]

@lbensen22 You could also programmatically add the row items to an excel table using power automate instead of adding it to a SharePoint List or add the data to both, but I always prefer using the lists.

I am not sure what all the conditions are for in the flow, but if you want to track tasks specifically, I would add a "Create item" action right under the "Update task details" card unless you need additional data that is identified later in the flow.

So, I would create the Microsoft List, call it whatever fits your scenario, and create the columns as you see fit. Then, add the "Create item" action right after your update details action, select your list, and fill in the information that you require. I would recommend adding a Task ID column so that you have the option to group by task id if needed in the future. I would also link get the link to the task as you see below so that you can easily get to the task. See an example of creating a tasks below and then tracking it in a Microsoft List:

This is what the Microsoft list looks like when it is tracked:

After that, you can then create another flow to track when any task is marked as completed, see here (My flows have error tracking in them so it does not have to be exactly the same as below):

Regarding tracking any other statuses, there is only a trigger for when a task is completed. There is certainly a way to check and update, but it would get slightly redundant since the users would update the task status in the planner app and not in the list. We could just update the list item when the task is completed and change the "Status" column to completed for a specific item by checking for IDs, but you lose a little bit of transparency that way.

Let me know your thoughts! I just track all action items in all flows and filter in Power BI if I want a metric for total tasks created vs task completed, etc.

[JezHolland]

Just to add to the above solution.  I have built a separate list that effectively baselines my planner. It checks that they both match on a recurring basis. Based upon this I can check the changing of status of tasks, and also the % complete, updating the baseline data if they have changed. If they do change this then updates my master reporting list.

The reason I do this is complicated, but there are reasons for me doing this.