Calculate progress of tasks in SharePoint using Power Automate
Today, I was asked about how to calculate progress on a task list in SharePoint using Power Auotmate
The requirements
Imagine that each task goes through 5 phases and we now want to get a percentage complete column filled.
My first thought of course was to use calculated columns, but in this case there were actually nearly 30 phases to go through. Calculated columns might be an options but getting an expression written for that would be too complicated for me.
So, could we use Power Automate to do this job?
Time for a bit of a trick here.
First of all I’m going to make sure that my Phase 1 – Phase 5 are choice columns rather than Yes/No columns. This is to ensure that I’m actually going to get Yes/No values rather than true false values.
We also want to make sure that none of the other fields on my items contain the letters ‘Yes’. So no field using the word Yes and no values with the word Yes.
Creating the Progress flow
Within my flow I’m going to start by adding a trigger When an item is created or modified.
Then I’m adding a few compose boxes. One for Number of Yes values. Then a second compose for the percentage complete.
The finally I will update my list items when the percentage has changed. This will give us a flow like this:
Time to look at that into a bit more detail.
Ok, the trigger is simple. Just select the site and the list and we are good.
The first compose box does a large part of the trick here.
We just need the following expression to find out the number of Yes values within the item that was just created or modified.
sub(length(split(string(triggerBody()),'Yes')),1)So we’re converting it to text. Then splitting it by the word Yes and then we can count the number of items in the array given to us.
Calculating progress
Now that we have the number of Yes values and we have the number of phases in total we can calculate the percentage marked complete
div(outputs('Number_of_Yes'),float(5))Note that we have to convert either the Number of Yes values or the total number of phases to a float. Without doing this Power Automate is really handy and rounding the result to the nearest whole number.
Updating the item
Now the final step of the solution, updating the item. Before updating the item we will need to compare the current value of the Progress field with the new value of the Progress field. This is to ensure that we don’t keep triggering the flow while now update to the phases is made.
Continue Reading Pieter Veenstra’s Article on their blog
Calculate progress of tasks in SharePoint using Power Automate
Today, I was asked about how to calculate progress on a task list in SharePoint using Power Auotmate Imagine that each task goes through 5 phases and we now want to get a percentage complete column filled. My first thought of course was to use calculated columns, but in this case there were actually nearly 30 phases to go through.
Blog Syndicated with Pieter Veenstra’s Permission
More About This Author
Array
Modern Workplace26 March 2024Receive the available storage within your SharePoint Online tenant- Modern Workplace20 March 2024Options for Documenting Your Power Apps: Comments, Code, and Controls
- Modern Workplace18 March 20242 ways to duplicate SharePoint Lists to support your Power Apps
- Modern Workplace14 March 2024Update a Hyperlink Column in SharePoint with Power Automate
