If you’ve been following my series on how to get Planner data (basic Planner, not premium) with Power Automate, you may have noticed that there is not a “completed by” user name or ID field available as a dynamic content card. We can still get the completed-by data using direct references to the JSON output for the “get task details” step, though! Here’s how.
Update the Power Automate flow to get the user profile for the completed-by user
Inside the for-each-task loop, under the “Get task details” step, add a condition. This condition will check to see if the completed-by user is blank for the task or not before it tries to get the profile data. For the condition, enter this expression and check that it is not equal to “null” (as an expression, not text, see other screenshot below). Expression:
items('For_each_task')?['completedBy']?['user']?['id']The above expression references the completed-by user ID from the for-each-task loop. Note that the loop card name is used in the expression, so if your loop is named something else you’ll need to make it match your name (replace underscores with spaces, see more about referencing JSON outputs with Power FX here).
For the “equals” check, enter “null” as an expression:
Add a step in the “Yes” branch of the condition for “Get user profile” – I’ve renamed mine to be more specific to “Get completed by user info”. Give this step the same expression you used in the condition, that’s the completed-by user ID reference.
Next, set the run-after condition for whatever step is under the condition set
If a user has left the organization, the step that gets the user profile will fail. We’ll address what we do with that in a minute, but first just set the run-after for whatever step is immediately after your condition set (in this case it’s “apply to each” to run-after both success and failure of the condition set in the ellipsis menu for that step.
Insert the completed-by user ID and display name in the data array
We already have an array set up to hold our Planner data, and we’re appending each task’s data to the array. After the last item in the array, add a comma and two more lines for our completed-by user ID and display name. For the user ID, use the same expression we used earlier.
For the completed-by user display name, which was an output of the “get user profile” step, wrap it in a coalesce expression – this will prevent the field from causing an error if it fails to get a display name for a given user.
The coalesce looks like this – just type coalesce() into the expression box, leave your cursor in the parenthesis, then go to the dynamic content tab and click on the display name card for the step that we used to get the completed-by user profile:
Then save the flow and run it to update your JSON file in SharePoint.
Update the Power BI query step
If you’ve been following the Planner data series on YouTube, we pull our JSON data into a data model there. That data model expands the JSON file, which uses field name references – meaning for it to pick up the new column, we need to update the expansion step. Make sure to use the “refresh” button in the query editor first, because it caches the data and likely won’t have the new fields in it unless you do this.
Open up the tasks table in the “transform data” UI and click the gear icon next to the “expanded column” step. Then check the boxes next to the completed by user fields:
If you have a “remove other columns” step, update that the same way.
Close and apply! You now have completed-by user data for your tasks.
What about Premium Planner?
If you’re using premium Planner, which is a totally different beast and has the data stored in Dataverse, I don’t believe that that actually has a completed-by property… just a heads up if you came here looking for that! There is a history table for tasks, but it’s “elastic” type in Dataverse and it doesn’t allow Power BI connections. The tasks table will, but the history table will not. Completed-by isn’t something that shows in the premium Planner UI, anyways, so it’s not a total loss (imo it SHOULD be there, but that’s a tirade for another day).