Get Planner Task Details with Power Automate - Assignees, subtasks, buckets, dates!
HTML-код
- Опубликовано: 17 дек 2023
- This video creates a Power Automate flow to extract all of the Microsoft Planner tasks and details and outputs them into a file stored in SharePoint. This includes assignee name and profile, task description, status, buckets, subtasks, and the colored flags/tags. We can connect to the data using Power BI to put it in a table or Gantt chart and then schedule refresh on the dataset!
Here's the next video in this series on how to model and visualize the Planner data:
• Report on Planner Data...
And how to get Planner data for ALL your groups and plans:
• Get Planner Data for A...
Or, you can send Teams or Outlook notifications asking assignees to update specific tasks from Power BI:
• Use the Power Automate...
Or opt to send all tasks for a given assignee in a single email/chat:
• Send STYLED Lists or T...
0:35 Intro to Planner task data
2:10 Create the Power Automate flow
5:28 Loop over tasks to get details
6:38 Get assignee names (and error handle profiles that don’t exist)
15:00 Get Planner flags data
17:55 Store the data in a file in SharePoint 
Наука
UPDATE: I forgot to mention that if you're doing the run-after configuration for if the "get user profile" step fails, you also need to set the step directly under the assignments loop to run after success AND failure, because it seems as though any failure inside the loops causes the loop to think it's a failure (there's a joke in here somewhere about life and failures). There's a followup video showing the process here: ruclips.net/video/mqGFbIGpCcw/видео.htmlsi=S6QO2B4-Nm91CI0w&t=704 or see this screenshot: christine-payton.com/wp-content/uploads/2024/03/runafter-config.png
Also if you have more than 400 tasks in your project, open up the "list tasks" settings and enable pagination and set the threshold number to higher than your total number of tasks so that it will get them all. If you experience issues with slowness, set the degrees of parallelism to 1 on the "get task details" loop settings. Part two, visualizing this data can be found here: ruclips.net/video/eEmpzueZfqY/видео.htmlsi=QCKeWjjUyhAXVgJt
is this the case: The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(item()?['bucketid'], items('For_each_Task')?['bucketId'])' failed: 'The template language expression 'equals(item()?['bucketid'], items('For_each_Task')?['bucketId'])' cannot be evaluated because property 'bucketid' cannot be selected. Property selection is not supported on values of type 'String'
@@michelebuzzi4545 It sounds like you stored your buckets in a string variable instead of an array variable - in my flow, I appended the bucket information to an array variable. Try switching to that?
@@bi-ome very strange, I declared Buckets variable as an array at the very beginning of the flow just as you did. I also have this error on the right corner 'Action 'Get_user_profile_(V2)' failed'. I don't know what to do honestly, I'm in a loop of fails :D
@@bi-ome fixed, I used the wrong brackets! all is fine now
@@michelebuzzi4545 aha! good catch :)
How has this video not got more likes? Admittedly realising there is a need to see Planner in PBI is a niche, and just following, never mind understanding, the steps is a feat (I only managed to succeed after 4 attempts and only with the video set to 0.5x speed).
This is a uniquely brilliant YT tutorial delivery. I know it's unique because after my 3rd failure almost broke me 😊, I was hopelessly scrabbling around the internet looking for an alternative.
There are none. This is the way!
Ha! Thank you for your kind words. This process also almost broke me, I had to re-record and republish it at three times figuring out all the pieces to get it right. It frustrates me that it’s this complicated as much as it does you, trust me!
@@bi-ome I totally trust you. Its clear there's no more or less process info here than is needed. Its so well presented. And so hard for the novice to follow- Power Automate is not easy. The persistence pays off though, so thanks again for your willingness to share this info!
If this helps anyone else, this is the order the labels appear. At the back end "Grey" is spelled "Gray", even though the label shows possibly the regional spelling.
Pink
Red
Yellow
Green
Blue
Purple
Bronze
Lime
Aqua
Gray
Silver
Brown
Cranberry
Orange
Peach
Marigold
Light Green
Dark Green
Teal
Light Blue
Dark Blue
Lavender
Plum
Light Gray
Dark Gray
{
"taskId":@{items('Apply_to_each_2')?['id']},
"taskName":@{items('For_each_task')?['title']},
"taskDescription":@{outputs('Get_task_details')?['body/description']},
"taskProgress":@{items('For_each_task')?['percentComplete']},
"taskStart":@{items('For_each_task')?['startDateTime']},
"taskEnd":@{items('For_each_task')?['dueDateTime']},
"assignees":@{variables('Assignees')},
"subtask":@{outputs('Get_task_details')?['body/checklist']},
"buckets":@{outputs('Compose_Filtered_Buckets')},
"Pink":@{items('For_each_task')?['appliedCategories/category1']},
"Red":@{items('For_each_task')?['appliedCategories/category2']},
"Yellow":@{items('For_each_task')?['appliedCategories/category3']},
"Green":@{items('For_each_task')?['appliedCategories/category4']},
"Blue":@{items('For_each_task')?['appliedCategories/category5']},
"Purple":@{items('For_each_task')?['appliedCategories/category6']},
"Bronze":@{items('For_each_task')?['appliedCategories/category7']},
"Lime":@{items('For_each_task')?['appliedCategories/category8']},
"Aqua":@{items('For_each_task')?['appliedCategories/category9']},
"Gray ":@{items('For_each_task')?['appliedCategories/category10']},
"Silver":@{items('For_each_task')?['appliedCategories/category11']},
"Brown":@{items('For_each_task')?['appliedCategories/category12']},
"Cranberry":@{items('For_each_task')?['appliedCategories/category13']},
"Orange":@{items('For_each_task')?['appliedCategories/category14']},
"Peach":@{items('For_each_task')?['appliedCategories/category15']},
"Marigold":@{items('For_each_task')?['appliedCategories/category16']},
"Light Green":@{items('For_each_task')?['appliedCategories/category17']},
"Dark Green":@{items('For_each_task')?['appliedCategories/category18']},
"Teal":@{items('For_each_task')?['appliedCategories/category19']},
"Light Blue":@{items('For_each_task')?['appliedCategories/category20']},
"Dark Blue":@{items('For_each_task')?['appliedCategories/category21']},
"Lavender":@{items('For_each_task')?['appliedCategories/category22']},
"Plum":@{items('For_each_task')?['appliedCategories/category23']},
"Light Gray":@{items('For_each_task')?['appliedCategories/category24']},
"Dark Gray":@{items('For_each_task')?['appliedCategories/category25']}
}
Thanks Christine, I'll be trying this out.
Now that I've re-watched this video a few times and rebuilt this flow a few times as well, I've realized that my mistakes were in not CAREFULLY noticing when/where you click on "Next Step". I had nested loops in there and couldn't figure out why the flow was failing. You move fast (which is good)...thankfully pausing the video after each step made me realize that I was inserting steps in the wrong locations.
Thank you for posting this walk-through! My next walk-through challenge: put ALL Planners into ONE Json file.
Good job! It’s hard for me to know what to go with on the tempo, I worry people will get bored if I go too slow. 😂
I need to remember to turn my mouse clicky highlighter thing on good point!
@@bi-ome Your speed is spot on.. I am just slow as a newbie so plenty of pausing, and re-watching. I found the 3rd time of building from scratch i was just about able to keep up and starting to figure how it all works.
Perfect timing, have been putting off trying to figure it out for weeks and now you come along, thank you! I DO want to get this into excel.. So I'm trying to not focus on your final comments!
LOL! On the plus side, once you have it in Power BI you can just export the table to Excel there if you want to. But if you're willing to do away with the multivaue fields (or spend the time to append the values to a string), it would be pretty simple to just drop the array variable into a "create csv" action. I find working with Excel files in Power Automate to be pretty aggravating. 😅
Just wanted to say a big thank you to making these videos!! I was trying to solve this over 2 years ago and could never figure it out.
Aw thanks! If I tried this 2 years ago, I would also have not figured it out. 🤣 There's something about wrapping your brain around how Power Automate uses JSON that really unlocks a lot of possibilities...
This is amazing, thanks!
Hi Christine. Great and helpful job. Is there any way to collect all tasks across all groups?
My guess is that it would be possible to get all plan IDs with the HTTP request action using the Graph API (learn.microsoft.com/en-us/graph/api/resources/planner-overview?view=graph-rest-1.0#container-type-microsoft-365-groups) and then loop over those ID values, using them for the "get tasks" in the custom value field. I haven't tried it yet though!
I got this up and running, video will be ready Tuesday!
It uses the "get groups I own or belong to" and "list plans for a group" - hypothetically you could just get alllllll groups with a different action, but the account that runs the flow has to be a member of the groups to get access to the plan data, and there can be a lot of groups, so if you do *literally* all it'll get a little messy. 😊
Hi Christine,
First off, I just wanted to express my gratitude for your amazing Power Automate videos-they've truly been a lifesaver! Your clear explanations and practical examples have made navigating Power Automate so much easier for me.
I do have a question that I'm hoping you could shed some light on. What would happen if a Power Automate flow, designed to send Microsoft Tasks to Power BI, exceeds the limit of 400 tasks? In my case, I'm primarily interested in ensuring that only the most recent tasks (upcoming and recently completed ones) are displayed in Power BI, so having a task limit isn't a problem per se. I'm just concerned about potential disruptions or issues that might arise once my planner exceeds 400 tasks. I want to make sure everything will continue to run smoothly without any hiccups in the near future.
Thanks again for all your help and guidance!
Aw thanks 🤗 You can get more tasks by turning on pagination in the settings for the “list tasks” action and setting the threshold to as many as you need (I think the apply to each loop is limited to 5k, so up to that). You might want to set the parallelism on your get details loop to 1 to avoid throttling if you have a lot-
Thanks so much Christine - These videos are invaluable. My one question is, would you consider making a video of how to utilize the "Get Plan Details" action to pull in the names given to the colored labels aka categories so that the final PowerBI could contain the appropriate labels for each plan? I have been trying to figure it out myself, but I don't have your level of expertise! Thank you.
They are in the outputs by their colored name, so you would need to add a line to the data array for each color you're using... the thing is that the action doesn't give you the flag names at all, it gives you true/false values for the color of the flag. So when you record it in the array, you can assign it the label there if you want to. I think in the example here I yanked the flag columns from the array into their own query with the task ID, then did an unpivot other with the task ID selected - that gives you one row per flag with whatever you called the flag in the array as the value. Then you can relate on task ID to use as a filter, or concatenate them with DAX into a calc column like we do with assignees. Check out the part 2 video if you haven't seen that one yet, pretty sure I covered flags in there--
The flags are a total pain to work with this way though - if the flags are super important to you, you might consider using premium Planner, because that has the flag names in the Dataverse table they are stored in and getting at the data is much easier without using Power Automate at all.
You are amazing! Your videos are perfect! From today you are the best like to Young Shane !
You are unique RUclipsrs that are able to teach something !
LOL thank you, that is quite the compliment, Shane is awesome! 😊
@@bi-ome today I finished the report on power bi following your video, the result is perfect! if we follow your instructions exactly we will never have problems! with someone else, either the instructions are trivial, or it doesn't work!
How can I support your channel?
@@cristiandonarini159 That’s great, I’m glad it worked for you! If you want to support my channel, just keep doing what you’re doing - watching and engaging with the content. The algorithm is designed to promote things that people value, and it measures that by how much people watch/like/comment. ☺️
Great, it just fits into what I was trying to do!!! I had it already from your previous video, but this update gets the full package of information I need for my Gantt chart! The table with the detailed information in Power BI is also an excellent complement to the Gantt chart! The only thing I am still missing is that fix on the Deneb chart, because I used the buckets as projects instead of phases. This way, I can get a full overview of my projects and related tasks, but they are not properly grouped...
Thank you so much!
Sure! I am planning on looking at the Deneb soon here, I have a cert exam this week + day job, so it will take me a bit to get to it. 😅
Yeah so I tried this and it is behaving really strangely - not sure if user error on my end, but I need to fight with it a bit I think. My method was just renaming the bucket column to whatever the template was using for phase and applying it, it kind of worked but all the tasks went under one bucket for some reason. 🤦♀️
Whoop got this working, user error on my part haha - going to try to record the how-to soon.
Hi Christine, Thank you for this guide, it's been a valuable reference. I want to add priority from the task details but did not see any dynamic content for appending the array variable(Planner Data) covering the priority values. Do you have any suggestions to add Priority to the json output?
Yep! That gets covered in a later video, it's not in the dynamic content but it is in the output for the step so you can reference there - here's the timestamp link: ruclips.net/video/mqGFbIGpCcw/видео.html
@@bi-ome Worked perfectly, Thank You !
Thanks for the videos Christine, very helpful and well explained. Can you provide any advice about how to get 'Created By'? I tried adding it like an assignee but I get an error saying it's an object not an array.
Also loved your video on pulling this into Power BI, great advice about how to handle the subtask checklist.
Thanks! For created by, you don't need to loop on it since it only has one value. You should just be able to use the get-profile action inside the loop on tasks and feed it the "created by user id", then stick the display name output from that in the data array. :)
@@bi-ome brilliant, thanks. Got that working now.
Thank you for this video! Got everything i need coming through now - i appreciate your help! My next challenge is to bring through multiple plans through without repeating the same flow over and over. Is this something you have come across?
It should definitely be possible! You just need to add the whole shebang in a loop over a list of the plan IDs, and somewhere in the append to array attach the plan name/ID so that you know which data goes with which plan. I’m planning to try it out sometime soon here (this isn’t my day job so have to work it in 😅).
@@bi-ome Thank you! Will also keep an eye out on your work :)
Thank you so much, Christine! Learning a lot from your videos e tutorials. Please, keep this work on! :D I have an issue: my JSON file seems fine, with all the information I want, but when I open it in Power BI, only part of the tasks are loading. Only the first task of each bucket appears. What could it be?
It's hard to say without looking at it, does the JSON file have all the tasks in it? I would search for a few by name in there to make sure they are there, refresh the query in the query editor, and make sure you don't have any filters applied there.
this is awesome! just in time i have a task similar to this! thank you so much! by the way is there a way to add the "priority" (Low, Medium, Important, Urgent)?
That is a really great question, I spaced on that field because it's not in the dynamic card list (why is it not in the list?? I don't know!). It IS in the output of the List Tasks step though, so what you can do is just add another line to the "append to array" step with all the fields that is "priority": and then insert an expression that references it (not just text, need to add it as an expression): items('For_each_task')?['priority']. Note that the first part in the items parenthesis needs to match the name of your loop, with underscores instead of spaces if you named it something else. I tried this and it worked - it returns a numeric value, though, so you'll need to tack on another conditional column in Power BI to convert the number to the actual text value of the priorities.
@@bi-ome Hi Christine! This video is awesome, thank you so much for this helpful information. I performed the task above to bring in the Priority but can't seem to get the new column added into PQ. I checked that the new Priority column is present in the JSON file on my Sharepoint and I performed a refresh on the dataset in PQ but can't seem to find that new column. Is there something else I need to do to make that column available?
@@DallasBridges If you’ve refreshed the query view (not the dataset - there’s two refreshes!), you should be able to see the field in the “remove other columns” step in the gear icon for it. I would give the query another refresh with that step selected and then edit that step to include the column assuming you can see it there. You might double check that the source step is referencing the JSON file you’re looking at too (eg the desktop version vs the SP version).
Hi Christine, these are a brilliant series of videos, thank you. One thing I'm struggling with which I notice doesn't happen to you is at 13:40 when adding the "taskId" into the append array, I get a new nested 'apply to each' appear as soon as the value ID is selected. I'm using the old version, any ideas how I can prevent this?
Are you in an “apply to each task” loop that is looping over tasks to get details? If you are it shouldn’t add another loop. Also make sure you’re in the classic editor, modern likes to add extra loops.
Hi Christine, when I apply this flow to one plan that has hundreds of tasks that have been completed, the flow times out at the For each task action. When I change the List tasks and List buckets to another newer plan with fewer tasks, it succeeds at this action after 4 min and immediately creates the JSON file afterwards. Could this Flow be adjusted to compensate for larger plans? For example, could it check to see if a completed task is less than a month old and exclude others that are older?
Yes, what I would do is just filter the array you get from the output of the "list tasks" step, then loop over the rows of the filtered array instead of the full list tasks output to get the details.
To filter on a relative date, you can use AddDays() to UtcNow(), adding negative days (because there's no subtract days... lol...). That gives you a date for comparison to your completed date. If you have any trouble with the comparison between dates, double check to make sure they are in the same format - there's expressions to change formats with FormatDate() and FormatDateTime().
Hi Christine! Many thanks for this video! If you could comment why this PA process extracts only ~3 months of Planner data? Also, is there a way to construct per Bucket analysis - like average time of a Task in certain Bucket, average time per Assignee per Bucket (avg time user processes a task on each step) and total time of a Task in each Bucket? I used the Bucket Name variable to construct the analysis, but not sure if it is correct? So I'm thinking - maybe it would be Get Bucket details PA run?
How many tasks is it getting? If it's 500, you're just hitting the pagination limit. You can enable pagination on the list tasks step and set the threshold above what you think you have as a workaround. Usually if you have that many it's good to set the degrees of parallelism on the loops to 1 also.
Hi Christine. First of all, thanks for your videos, they're very helpful. My question is; I'm managing my project at one time and I'm using "Channels" as "Projects" so there are too may channels exist in my Team. At the end of the day, I'd like to see all open tasks - for example - in one report in Power BI. Is it possible to do that? Thanks for support.
Thanks! You should be able to do that. I'd try adding a step at the top to "list plans for a group" which should get you a list of all of your plan IDs. Then you should be able to use that inside a for-each loop over all of the other actions (use the plan IDs as dynamic content), and say for each plan ID list the task and get the details and append it all together. Make sure to add the plan name/ID to the data array so that you know which tasks are part of which plan. I haven't tried this yet but my guess is it's possible, I'll probably try to do a video on something similar in the future.
@@bi-omevery well. I'll try to do that but most probablt can't do it without your video 😅 I hope it's easier than I thought. Thank you Christine 🫶🏼
@ You can do it!! Trial and error makes the Power Platform world go 'round, lol 🤗
Incredible tutorial. Do you know how we can record the LastModifiedDate of tasks?
Good question, it looks like it's not surfaced in the API at all, so we can't currently get it. Someone did create an idea to vote on here though, if you want to vote for it: techcommunity.microsoft.com/t5/microsoft-365-developer-platform/microsoft-graph-api-planner-expose-the-quot-last-changed-quot/idi-p/2938283#M447
Strangely the subtasks have a last modified date on them, though...
Great video, exremely useful. I get duplicates on tasks, where the power automate flow seems to not filter the buckets properly. All buckets are listed on all tasks in the json import to POwerBI.... Any pointers on what could be wrong here?
(I could not get the number of for-loops to match your video, even with old/new UI combinations...)
Solved it! Messed up the nesting of the for-loops....
Easy to do! Thanks for mentioning you fixed it, I think most times people delete their comment and then I go to respond to it and get very confused why I can't find it when I click on it lol.
Hi Christine, this video is fantastic! I'm wondering if you could show a short clip or walk through the steps to get all of the Planner in a group? Instead of choosing one Plan Id in 'List tasks' step, what expression should I write to get all the Planners from one group?
Why, yes, that's part 3 in this series 😉 ruclips.net/video/mqGFbIGpCcw/видео.html
This one gets all your groups and all your plans, but if you don't need *all* you can just skip the step that gets the groups and loop over the plans in a single group instead.
@@bi-ome Thank you!!
Hi! Your tutorial just saved me! Thanks a lot! I just got a little trouble with the process. My planner have about 200 tasks, but the json file have over 5300 lines, the majority just have diferents TasksIDs that I don't need. The flow takes around a hour to run and I want to filter those files. Any ideia how to solve it?
The size of the JSON file likely isn't causing the slowness - it's the for-each loop over the tasks to get the details and the assignee info. The for-each loops are usually slow, but since the "list tasks" doesn't have all the info we have to make an extra call to get the details. If you want to speed it up, you could add a filter array step on your list tasks results and filter out any tasks you don't need (e.g. you could filter on status or date there), then loop over the rows of the filtered array to get the details instead of looping over all tasks. If you have a lot of tasks, you could also be hitting the throttling limit on how many calls you can make in a particular period of time, which conversely setting the parallelism on the loops to 1 to slow the flow down can actually speed it up if the throttling is happening.
This was a huge help Christine. Do you know how to get the values for the repeating attribute of the task?
Thanks! I don't think the repeating data is in the connector or the API. Probably because that's a relatively new addition to Planner... best I can do is this person's solution, which is janky at best (but hey, we're already somewhat out there on the jank limb at this point, may as well go all the way? 😅). powerusers.microsoft.com/t5/Building-Flows/Planner-Task-check-for-recurring-tasks/td-p/2290770
Thank you!!!! You're the best
Hi Christine, is there a way to format the dates in a uk format in the flow? As when i put them into PowerBI there is an 'unable to parse' error and only shows uk date if the column is text (Sorry i did comment before but deleted my comment as i thought i fixed it but i wasn't able to). Thanks in advance
It might be the region the group is set to? I would try the formatDateTime() expression around the date field and see if that does the trick: learn.microsoft.com/en-us/troubleshoot/power-platform/power-automate/how-to-customize-or-format-date-and-time-values-in-flow
You can also addHours() to adjust to a specific timezone if it’s in UTC.
Thanks Christine. The modified video helps to consolidate all into one. However, I am having trouble with the flow runs. Although they seem to succeed, each time the flow runs to hours. Anyway this could happen faster?
Try setting the concurrency in the settings for the loops to 1. Usually when the flows take forever, it's because you're getting throttled by the service for making too many calls too fast, so slowing the flow down can actually make it faster (?!).
@@bi-ome Better than before. now it has come down to 30 mins. Appreciate your help Christine. Thank you so much!!
Hello! This video is an amazing ! For some reason when I test the flow, it fails on the for each task loop (the second loop of the flow), how can I fix this?
I've also noticed that the flow has timed out on several occasions.
What is the failure message? It shouldn't fail to "get" details for any given task, is it maybe one of the steps past that in the loop? I have heard of people having issues with very large plans, I didn't account for people having thousands of tasks when I made this but it appears to be a thing. 😅
Thank you so much for this 🙏
Edit: I saw comments aren't possible currently. Very annoying as this is one of the major issues I'm trying solve from old excel trackers by moving to planner!
As a consolation, you could submit the idea here - it would need some upvotes, but Microsoft does use this to add features. :) feedbackportal.microsoft.com/feedback/forum/a1a93b69-2f1c-ec11-b6e7-0022481f8472
Hi Christine, thank you for doing step-by-step video tutorials. Really appreciate your instructions. I have 2 questions:
1) The flow ran succesfully, however in output file some assignees records are duplicate for each task. I check the flow details: The first Append to array variable assignees got 1 value only, then the step of Append to array variable: there are 2 values for assignees in there.
2) Is there a list showing all the fields that we can extract out for buckets, tasks, assignees etc.
Thank you again
Did you add a step to reset the variable at the end of the loop? If you don't do that it will tack on people every time it loops and by the end your list will be quite long. :)
@@bi-ome brilliant, thanks Christine, I did miss that step. Now it's working like a charm * sending flowers *
@@bi-ome hi again Christine, I would like to check with you.
Instead of a json file, can we extract the output into Sharepoint List (last 2 steps of the flow) with different columns title task ID, task Name, Plan ID, Plan Name...
I only managed to put all the output into only 1 cell for the Sharepoint List, not into different columns.
Thank you in advance
Is it possible to add the email itself as a attachment in the planner task? i only find videos where the attachment of the mail can be imported, but i want the whole email :/ the reason why is that i can click on it and answer directly and write a mail... do you know that? :)
Hi Christine when I duplicate your process here my flow just ends up running and running without ever spitting out a .json file. Do you know what might be wrong? Thanks.
What step does it get stuck on? If you open up the flow it should show you where the issue is, it’s hard to say without knowing that-
Hi Christine, Thank you for the video. when appending assignees I get null arrays for random tasks at each refresh even though we have assignees for all tasks. Can you help me with this?
Are those assignees still with your organization? Are you pulling profile fields that have a value 100% of the time? I would check a few of the user profiles that are blank where you expect not to be and make sure they have values for the fields you're referencing (not companies actually fill out the M365 profiles).
Hiii awesome content!!! I want a help....please show us how to extract the comments from all tasks??
It's not currently possible to get comments to my knowledge! There's a thread on it here where you can vote for the feature though: powerusers.microsoft.com/t5/General-Power-Automate/Microsoft-Planner-Comments/td-p/1950838
Hey Christine,
Amazing guide! If there was an additional way I could reach out to you I'd love to. I'm having the typical error that you mentioned in your pinned comment, however when following the guide, it doesn't seem to resolve the issue. I'm still failing on the assignees failure loop, and I can't seem to correct it. I'm adding the run-after configuration to the "Compose Filtered Buckets" step, but not really getting any farther unfortunately. There appears to be a little information icon that reads "View when "append to array variable Assignees 2" will run under "Configure run after" but that makes no sense to me lol
Thanks! The run-after config goes in the "filter array" step, not the compose step - it's the first action after the assignee loop. I took a screenshot of where to do it if that helps: christine-payton.com/wp-content/uploads/2024/03/runafter-config.png
You can submit the contact form on my website if you want to get in touch (link is on the about page), or LinkedIn. If it's a question about a video though it's best to comment on it in case other people have the same problem. :)
@@bi-ome Thanks for the comment response! So I did that...but I guess I just wasn't patient enough. My flow took about 10 minutes to run after applying the fix. I appreciate you! Keep blazing the trail and showing us how! :)
@@bi-ome do you by chance have a video of you working with this data in PowerBI? I would love to figure out how to organize this data, and utilize it. I'm a newbie to PowerBI because up until now I've been an avid Tableau user.
@@brandontheITguy You mean like this one? ruclips.net/video/eEmpzueZfqY/видео.html
This video is a 3-part series, they are in the video description if you want all the related ones. 😄
Thanks!
Aw thank YOU!
I would like to add the "priority" in the step "Append to array variable PlannerData", but it doesn't list in the dynamic content - is there any way to get around it?
Yes, I have a section on that in the follow-up video here :) This link is to where to record it, there's a bit later on where you match it back up to the text value (it comes through as a number): ruclips.net/video/mqGFbIGpCcw/видео.html
Thanks for this awesome video. I am a relative PowerBI newbie and this enabled me to create a pretty complex set of data for reporting on for my team just by following your video.
I do have a question - many of our Planner tasks have up to three tags and I want to be able to report on tasks which are tagged with more than one particular tags - including ideally, a slicer so the user can see the data by the tag combination of their choice or choose from set combinatons. e.g i want to see how many tasks are tagged with both 'Marketing' and 'Customer Journey'. Is there a way to do this?
That's so great to hear! Have you gotten to part two in this video series yet? In part 2, we model the data to create a flags dimension table. Once you have a flags dimension related to your tasks table, you can use the flag values in a slicer on the page, so if you have a visual that is counting tasks it'll show how many have a particular value... getting it to count how many have BOTH is more complicated. The easiest way to handle that in my opinion would be to just use the filter pane and change the filter type to "advanced" for the flag name column in the dimension table. That will let you use "AND" to filter on combinations.
@@bi-ome Thanks so much for this. Yes I have followed all steps from both videos.
Using the advanced filter, I am able to create an 'OR' filter for the flags but the 'AND' filter just returns a blank visualisation. Do you know why this might be?
I have an additional column in the table which returns number of hours assigned to a task from what we write in the 'Notes' pane in Planner.
So what I need is e.g how many hours are assigned to unique Tasks that are flagged with both "Flag A" and "Flag B"
N.B I have pivoted the data in the Tasks Table so there is one row per flag.
@@Polly-rp9sk Ohh, if it's one specific flag combination that's more of a DAX thing. You don't want one row per flag in your main tasks table, generally, that's why we split it into its own dimension table - if you expand it inside of tasks, you can't sum things on tasks properly because the thing you're summing will be duplicated. The way to do this would be to have the flags expanded in another table but related to tasks on the taskId field, bidirectional if you want to filter tasks on flags. Your DAX would be a measure that uses filters on CALCULATE() for rows that have both flag values in the related table. CALCULATE() with filters is the #1 piece of DAX that gets used all the time so it's worth learning the syntax for it.
Thank you! As said before by so many users, your videos are very useful! I'm following this one, but when I readh 6:58, I find no "assignment". In fact, I don't have any item with the word "assignment" in it. Has it changed name, or could it be a different problem?
Thanks! Have you tried the search box in the dynamic content pane? For SOME odd reason, many things show up in search but you can’t get to them through browse. Also toggle off modern if it’s on.
@@bi-ome Thank you. I found it (I don't know how). Would you tell me how I can modify the routine to manage cases where there's no one assigned to the tasks?
@@LeonelGuzmanContinousLearning You don't need to do anything special for cases where there's no one assigned. Most of my tasks in Planner don't have an assignee - since we're appending assignee info to the assignee variable, if there's no assignees the variable will just be blank and it'll go into the data as blank for the assignee columns. It shows up on the Power BI side as "null".
Hi Christine...great video. I am learning alot. I seem to be getting an Error > Append to array variable(1) enter a valid json. What do I do to fix this? Its the 2nd branch we created where I am having an issue. I used for the "assigneeName":Unavailable"
Usually this means you forgot a comma or have an extra comma or some other typo. The last "line" should be no comma, the rest should have a comma at the end--
@@bi-ome haaaa...that's exactly what it was. Thank you.
Hi Christine, in regards to the flags as you call it have you tried adding value appliedCategory to the append to array? This works when I run it manually but doesnt seem to show with the schedule.
Ohh, is appliedCategory a list of applied tags or something? I was wondering what it was. Mine was mostly blank but I may have been looking at tasks without flags, I will check later. It shouldn't have different data when run on a schedule vs manually.
It is the written description of the tag, not the color. For some reason it works when I run the flow manually but not with the schedule. Happy to work with you on this.
@@vinnieb1235 So for me, it seems to just have the category numbers, there's some text in there but it doesn't seem to correspond to the actual label (eg these were blocked/pending but show as category1, category13). What does yours look like?
"appliedCategories": {
"category1": true,
"category13": true
}
I got this from a test manual run, not scheduled.
I just added it to the final append array.
"Category": value appliedCategories
@@vinnieb1235 right, I guess that makes it a little easier to tack them onto the array than doing them one-by-one, but do they have the actual text values for you? mine are like category1 etc, not the real values.
This sounds like what I need, but I've never used power bi. I'll try to copy that video, but could I use the json in SharePoint or just make SP List items for the last step? SharePoint has better security features, but I can't figure out sub tasks (planner checklist) migration.
The data lives in SharePoint either way - the later video connects to the JSON in a SP library. You could put it in a SP list but you would have to do a lot of finagling steps for the multi value bits (which is where you’re running into trouble with subtasks - this isn’t flat data). You also wouldn’t be able to flat out replace the items when the schedule runs in a list, it’ll just create new list items unless you do a series of steps around checking and updating each list item. The reason we use JSON is because the step outputs are already JSON and Power BI can work with that pretty easily, and all you have to do to update the data is overwrite the single file.
Thanks for the quick response. Before I dive into power bi, I'm going to try to use the JSON data to recreate the finished tasks in a different group for backup. It's unfortunate that copying plans only copies unfinished tasks, and you can only copy finished tasks one at a time.
@@dustforged If you're just trying to copy the tasks to another plan, I would just loop over the tasks and use the action that creates a task instead of doing anything with JSON files. Off the top of my head, I think you have to create it and then use the action that updates the task to fill in the details, because the create task option doesn't have all of the fields you'd usually want. You're still going to run into issues with creating vs updating the tasks, though, I honestly wouldn't use copying tasks as a backup method like this.
If you just want a record of what tasks were at any given point in time, you can also just use the JSON file technique and add a field to the data array called "dateTimeStamp" and put UtcNow() in it, then put the same datestamp (maybe format it with hyphens instead of slashes with FormatDateTime()) in the filename in the create file step - that way it won't overwrite, it'll create a new file each run, so you'll have a whole history there. You could use the Power BI folder connector to combine all the JSON files into a table and see exactly what task was what on what date. I get that you're resistant to Power BI as an option, but it is really good for mashing data together.
Hi Christine, great! I did exactly what u explained but in Power BI I see many records (1 row for each subtask that is linked to each task) for each task. Do u know how to find the issue?
Yeah, it sounds like you expanded the subtasks in the tasks table. What you want to do is duplicate the query, leaving just the subtasks column and task ID, then relate the two tables (1:many). If PBI auto-expanded the column in your tasks table, you can delete that step. You don't want them expanded in the tasks table.
ciao @@bi-ome, check it, but it seems I'm getting duplicated rows directly in json file.
Any clue? Otherwise, I will try to start again from scratch with a new Power Automate flow 😞
@@DavideBellettihowto Make sure your array variable that holds the data is using the Checklist dynamic content (if you hover, it should say body/checklist) - there are also "checklist item" and "checklist value" that could be causing weirdness if you grabbed those instead.
Thanks@@bi-ome ! I solved generating a new json file with different name...wierd 🙂
Hello Christine, thank you for helping me. I have another question, I need to take the information about card comments, because I need to follow some comments and dates form people and I don´t find how I can do this. Many thanks and have a good day.
The comments aren’t in the API currently, so we can’t get them with this method.
Thanks
Hi there, thank you for effort I was tasked to gather report which automate from planner followed you instructions seems I have problem with amount data it's 6 month task, how it would done this case.
You mean you have too many tasks? You could use "filter array" on the "list tasks" step to remove completed tasks, potentially. You'd then loop over the results of the filter array instead of on the "list tasks" output.
@@bi-ome completed tasks is essential for generating an accurate performance report, it seems we're encountering a timeout issue due to the large volume of data from the 6-month task span. please how Could we adjust the process to handle this data more efficiently,
@@kim12a How many tasks do you have? The issue is mostly that the basic "list tasks" connector doesn't get all the fields, it's the looping over each item to get the details that takes all of the time. Conceivably you could get the data you need directly from the API with the HTTP action to structure the query in a more efficient way, but I've not tried that method. The API documentation is available here: learn.microsoft.com/en-us/graph/api/resources/planner-overview?view=graph-rest-1.0
@@bi-ome thank you for assisting, I identified the problem it's not listing tasks, it's about get task where I'm trying to get 2k tasks above in 6 month could you have any tips to get through this please. currently due to my region can't access API since Azure is not in country category.
@@kim12a The only way I know of to work around not having the API access is to just break your queries into smaller queries and then append them together downstream. There's a "SharePoint Folder" connector in Power BI that you can use to expand a folder of JSON files, so you could run multiple flows to get different subsets of the data and then recombine them. You might also look into Azure Logic Apps, which is meant to be more scalable for large datasets, but I'm not sure if it has the same set of connectors or not.
Hi Christine,
first of all thank you very much for this great solution 💪
I had the flow up and running for 2 days and then suddenly without changing anything it started to fail because of an error "get user profile assignees". I also tried it again with the changes you mentioned that the run after variable needs to be set to run even if it was successful.
Do you have any ideas how to fix this?
This can happen if any of your assignees leave your organization, if you don't update the assignments. We *partially* error handled this in the video with the parallel branching, but I forgot to mention that you have to also set the first action after that loop needs to be set to "run after" success OR failure, because any failures in the loop seem to flag the loop as having failed even if one branch succeeds. It's revisited here if you want a walkthrough :) ruclips.net/video/mqGFbIGpCcw/видео.htmlsi=kH86SNxuKAhvptI4&t=702
@@bi-ome 👏Thank you! Now it works again ☺
@@Stefan_Guelden Need a party parrot emote! 🎉🦜
@@bi-ome Unfortunately the flow now runs without a failure but I also do not see any of my assignees anymore because the flow always chooses the run after loop 😥
@@Stefan_Guelden The parallel branch bit should have one side that runs after success and the other side after failure - that should force it to choose one side or the other and the successes won't go through the failure branch. Then the step after the loop should run after failure or success?
This video has been a lifesaver, and honestly pretty fun to build! I am having one issue though, when I input the JSON into Power BI, the 'taskComplete' column has a weird Date/Time format which causes an error message of "We couldn't parse the input provided as a Date value. Details: 2024-02-08T14:47:02.3200921Z." The date format is showing up differently from the 'taskStart' and 'taskDue' columns but I entered everything correctly in the flow. I have noticed that in the Planner I'm drawing from, the tasks giving me these issues have been struck through, as they have been completed already. Could this be the issue?
Yes, that format is Date Time Timezone but it comes in as text. You need to convert it to Date Time Timezone type first, then convert that to Date or Date Time. When it asks if you want to replace the conversion step with the new one, say no - it needs that intermediary, it can't go directly to Date Time. :)
@@bi-ome This worked! Thank you (:
Firstly, congratulations on the video, very good! But I had a question when trying to export my task plan, is the task limit only 400? How could we change that? Is it due to the Json file?
Thank you very much in advance!
Thanks! If you go into the ellipsis menu -> settings for the "list tasks" option, there is a toggle to enable pagination that will let you get more (set the threshold above what you need). 400 is basically the first "page" of API results.
@@bi-ome Thank you very much!! Mainly due to the quick response! Now I'm just going to test and pray that there won't be any problems, because the plan here at my work has almost 5000 tasks, and the 400 already took approximately 18 minutes, hahahaha. If it works but takes a long time, I can schedule an update every morning, for example.
@@rafaellara5415 Ohhh lol, that is a lot. I would be surprised if that works. I would set the parallelism on the loops to 1, or you will probably get throttled for trying to pull too much too fast. The thing you'll run into is that there is actually a limit on for-each loops, too, which I think is 5k (don't quote me on that). Is this in a single plan? Note that Planner has limits on how many tasks you can put in a single plan as well, so you may want to start breaking them out into smaller plans in the future.
@@bi-ome Hi, unfortunately, yes, there is an existing plan with approximately 4800 tasks, because in the Panel I have, I need to show all the tasks in the plan, including those completed some time ago, and this ends up being very cumbersome. Today, there is a person in my sector who exports this Excel plan to the network daily and my Power BI reads this Excel with these 4800 tasks. The idea of your video would be to export these 4800 tasks into Json through Automate, but honestly, I don't know if it will work, hehe, I'm testing it now.
Anyway, I'm testing it at the moment, if it lasts 1, 2 or 3 hours but works, ok, keeping this automation in the morning before working hours. If it doesn't work, do you have any solution? Thank you very much for your attention and quick response!
Excellent tutorial delivered in a very understandable manner - thanks. I have one issue in that my final output is dumping all of the content for that record of the bucket array into the bucketname field. i.e. instead of just the bucket name it dumps the following - [{"bucketId":"mafyIVDU3UuNV8hmWIhQ85gAK8os","bucketName":"First Bank"}] . Any poiinters.?
Sure, this is fine assuming you're using Power BI for the reporting, which was where we're going with this. Power BI will let you expand it out into columns - I have a video on that (part 2 in this series). You can also use an expression to reference the first item in the array and property values by their name if you're trying to do something else with it. I am having trouble finding any kind of good tutorial to link on the array references... guess that'll be a good future topic :)
@@bi-ome Found a simple solution in the end by accident. Compose using the index and the element name worked - body('Filter_array_varBucketNames')[0]['bucketName']
@@julianguegan2994 That makes sense, just getting the first item in the array basically? I’m so used to dealing with cleanup on the Power BI side that I tend to default in that direction :)
First of all big thank you for that video, I would never imagined how hard it is to get Planner data ready for PowerBI. There is one issue I have after I added the bucket information later to the already created Power Automate flow. While adding the "buckets" to the "Append to array variable PlannerData" step, I do not get the "Compose filter buckets" Outputs in the dynamic content for selection. It is just not there. What could be the reason?
Make sure you're not using the modern UI - there's a toggle to turn it off in the top toolbar. A lot of the dynamic content isn't in modern. Also make sure it's nested in the same place as in the video, if you have it misplaced inside or outside the loop it needs to be in it won't show up--
@@bi-ome Thank you Christine, it was nested wrong so it couldn't show up.
What about task priority? Can it also be stored to the sharepoint?
Yeah, it’s in the action outputs as a number, and you can map it back to a text value. I think that’s in the part 3 video?
@@bi-ome Okay thankyou very much!
can you please advise how to make it not to overwrite this JSON file in SPO directory? it gets always rewritten, i would like to have document for each run, great guide by the way
Thanks! If you don't want it to overwrite, just put the date/time into the filename with an expression. You can use FormatDateTime() with UtcNow() to put it in the format you want. If you want it in a specific timezone use AddHours() to add hours to utc.
@@bi-ome many thanks for such quick response !
Is it possible to add Planner task comments into the BI report?
Not right now, the comments aren't in the API/Planner Power Automate connectors
What if I have multiple plans in the same group?
You have to select a plan for "get tasks" - so just select the one you're trying to get. If you're trying to get both, that's a little more complicated. :)
Hello. Thank you for your useful video. I have a problem. When I get to the "For each task" step, the loop continues to run but does not complete. I set the degree of parallelism to 1, but the loop is still not completed. Can you help me?
How many tasks do you have? This technique is not really meant for thousands of tasks, if you have thousands I would recommend Planner Plan 1 licensing (which only requires a license for the person creating the project). That one has the data directly accessible in Dataverse so it can pull a huge number of tasks in comparison--
@@bi-ome Unfortunately, there are too many tasks. So I want to retrieve only uncompleted tasks. Can you help me on how to update this?
Hello Do you have a solution to get only ongoing tasks? @bi-ome
Hi, is there a way yo also get the attachments of the task?
You can get the hyperlink for the attachments in the task details output (references -> resourceLink). Keep in mind it’s multi value, so I’d treat it like the subtasks I think.
Thank you :)@@bi-ome
I followed all the steps diligently, and my first flow worked like a charm, just like the two copies I made after that. But then, the latest copy I created hit a snag. When I swapped out the Planner ID in the "list tasks" and "list buckets" steps for another plan I made, it didn't grab all the completed buckets or tasks. And when it reached the "append array to DataPlanner" step, which is the first variable, the assignees list came up empty. What am I doing wrong? it works perfectly to other planners, but not for the new ones.
Is the flow completing successfully? There's nothing in here that would remove the completed items, but some people have issues when they have a huge number of tasks
@@bi-ome The completion was successful. Trying to understand, I noticed that the issue stemmed from multiple loops introduced by the new designer. I made the necessary adjustments and resolved it. However, subsequent runs of the flow began to exhibit a discrepancy, with some tasks properly assigned and others left null.
Hi Christine, could you help me with something, please? I completed the entire flow and it worked in my test, but when I did it with the real data, it didn't fetch all the information because in my planner there are 820 tasks and it only extracted 400. How could I extract the other 420 tasks?
Turn on pagination in "list tasks" and set the number there to higher than what you expect - that will have it loop through in the "get" step to get them all. You might also set the concurrency on the loops to 1 to avoid throttling if you have a lot.
I'll try it, many thanks :) @@bi-ome
Hi Christine. I need to create a flow to designate the colored tags of a Teams task, the color of the tag must be determined by key words that are in the task description. Could you help me create this expression, please? Thank you in advance
You set the task flag using the colors and setting them to true or false… so I think this will end up being a bit convoluted, because you’ll have to have the action that sets that for each value you want to be using. You could use a switch or nested conditions… I’m not sure if you’re creating a task or updating a task but the process would be pretty similar either way.
I don’t think an expression will get you all the way there, but you could probably use it to get the flag value you want to switch on.
@@bi-ome I've already created a flow with a trigger from a booking appointment that generates a task in teams. Now I need this task to have a colored tag, and the color will depend on the response made by customers during the booking appointment. In the flow I did, these answers from MS Booking are in the task description. Should I really use a switch?
@@Thiago-nm5df The reason I suggest a switch is because each and every color flag is its own true/false field in when you update the task in Power Automate. There's not a property for "flag color" That you can set, it's for example "Blue" and "true/false" for the value and "Red" "true/false" and so on, so I think you'd need to have essentially a case for each of the colors you want to use.
@@bi-ome thank you! I'll try that!
Thank you @bi-ome so much. I have this issue which I don't know how to solve. My planner only has 300 Tasks at the moment, but it takes a lot of time in the apply each loop for some reasons. I don't know how to solve this. can you please guide me through it?
Thanks in advance!
How much time is a lot? It’s pretty normal to have it run 10+ minutes, if you schedule it the runtime isn’t a huge deal imo.
@@bi-ome It is more than 10+min but for some reason the files are not created even after 1 hour. My reoccurrence time is 1 hour for the schedule, but the files are not created even then.
@@maitripjoshi Can you check the flow run history to see where it’s at or what’s going on with it? Once per hour is a bit too frequent imo, Power BI will only refresh 8x per day on pro so your flow should probably only run that much or less. I would do 1-2x/day max personally. These things have a limit on number of actions run in a period too, you be be hitting the threshold.
@@bi-ome Thank you. Yes, I keep getting the mail that the performance is low so I will change it to twice a day and see how it works. thank you very much.
Hi, I have a planner with 700+ tasks. Now only the first 400 are reported in the json file. How can I ensure that all my tasks are included in the json file?
Go to the "list tasks" step settings in the ellipsis menu and turn on pagination, and set the threshold to above whatever the max tasks you expect to have is. You might want to set the degrees of parallelism on the loops to 1 to avoid throttling.
@@bi-ome Thank you very much, that's what I was looking for
Hello, thanks for your video which is probable the best for the subject !
Everything work fine but getting the user profile,
It SEEM to me i did exactly the same as you but I get the error "The execution of template action 'Profil_utilisateur' failed: the result of the evaluation of 'foreach' expression '@items('Pour_chaque_tache')' is of type 'Object'. The result must be a valid array." for my loop "profil_utilisateur" (which is in the big loop "pour chaque tache") and since i'm not super advanced in powerautomate, i don't understund where there can be a problem of data type ..
Thanks for your help !
Sure, make sure you are using array variables and not string variables, and using the "append to array" not "append to string" - they look very similar in the finder :)
@@bi-ome thanks for your reactivity ! I deleted and recreated them and I'm pretty sure my 3 variables are initialized as array and that i use append to array for the bucket loop and for the asignee loop but still
after some test it seem the probleme come from the apply to each loop inside whiche you add the get user module, it need an array instead of an object as input and it seem the "value assignements" of "task list" is not array-enough for PowerAutomate
Side note : after the first test run the "value assignements" change itself as "actual element" of the general "for each task"...
Side note 2 : it seem that the id of people which the task have been assigned to is always empty in the raw output I see after Get Task details ( I tried to add them in an Array) I think it's the end of my quest for this ^^
I want to assign multiple assignees to the task through power automate. How do I do that?
I have found the solution. It's as simple as using a ";" in between the emails
Thanks for sharing!
Hi! Having an issue with this.. My flow works fine but the data looks strange in power query, every task name is repeated on three rows with three different taskId's and these three Id's are the same for all the task names... Any ideas what i've done wrong?
It sounds like you expanded out either assignees or subtasks in the main query? Since those are multivalue fields, if you expand them inside of tasks, they will cause row duplication. That's why we put them in their own subtasks and assignees tables and related them back on the taskId. But in that case usually the task name would be the same for each ID, not different, so not entirely sure what's going on there... I would double check your append to array step too and make sure you have the correct dynamic content fields pulled in there.
@@bi-ome Thanks for your reply! I checked my dynamic content again and saw that I’ve picked the bucket id at one point instead of taskid. So problem solved! Thanks :) But every task is still repeated on thee rows though, but that’s easily solved by removing duplicates.
@@andersborglund2287 Great! It that does sound like you've expanded one of the multivalue fields if there's still duplicates. If you just remove duplicates, you'll remove whatever those multiple values were, too, if they expanded to new rows. I would step back through the applied steps in the query until you find which step is causing it. If it's assignees or subtasks, delete the step and duplicate the query to make separate tables for those things like we do in the video-- assuming you need those fields, anyway. :)
@@bi-ome Yes I know, but each task was multiplied by three (three rows with identical data) from the beginning. Very strange.
Btw, is it possible to set up a flow that sends messages/notifying assignees if their task is missing a starting date?
@@andersborglund2287 Hmm, maybe looking at the outputs of the flow steps would narrow it down a bit. For notifications, I did a video on sending emails/chat messages from this dataset with Power Automate here - what you'd do is just set up the table visual used to generate the DAX with the filter of the start date being blank, the rest of the process should be pretty much the same: ruclips.net/video/8YnBotHynD0/видео.htmlsi=q7rpNEs7GNkMTv0G
After 20 minutes the flow is still in progress, and I have only 125 tasks. Is that normal ?
Hmm, I would watch the flow history as it runs, it'll tell you exactly where it's spending the time and what it's doing. The flow I have that gets all tasks for all plans takes about 10 minutes to run, but it still has less tasks than you're getting so possibly. :)
@@bi-ome Everything is ok now. I did absolutely nothing, but now the flo take under 3 minutes to work.
Thank you for this helpfull work and for you answer.
Hello, I have followed your steps, but the final report comes out in 1 column on BI. Could you help me find the issue?
Does Power BI let you expand the column? I would open the file and see if it looks like valid JSON. Usually when it doesn't auto-expand there is a typo in the append to array somewhere, or a missing comma/quote.
I had the same issue once, as Christine said my error was, somehow there was a comma added wrongly in the append to array code that ended up in the json and therefore prevented PowerBi from expanding.
For the second "apply to each" loop I keep getting this failure message:
"ExpressionEvaluationFailed. The execution of template action 'For_each_Task' failed: the result of the evaluation of 'foreach' expression '@items('Apply_to_each_2')?['id']' is of type 'String'. The result must be a valid array." I have triple checked all the value id fields and such. Any ideas how to fix? Thanks!
Try looping over the "value" property from the output of list-tasks instead of "id"
Thank you for your response, it ran for 24 hrs. and I canceled it. So, I'll try to lay it out without a picture, but in the second loop, for tasks, after the loop for buckets, in the first "select an output" field I have "value", then a sub-loop "for each task" with "value", then the "get task details" field under that with "value Id" in the "Task ID" field. Then it moves on the to filtering termed the assignees split step. I have also verified it is not in the new designer UI.@@bi-ome
@@user-id5tc1wn3w Do you have a large number of tasks, or no? I've heard people say they have issues with 500+, not sure if that's the case here.
Yes, just short of 1200 (1016 Completed)@@bi-ome
Yes, there were over 1200. I went in and cleaned out a large number of the completed tasks. The planner that I would like to pull from averages about 1200 tasks per year and I would like to be able to report back up to 18 months.@@bi-ome
This seems ot be exactly what I need. when I import the JSON in to PowerBI all of my data is in one column though.
Are you able to expand it out? The next video in this series goes through how to use the JSON file, not sure if you’ve seen that one
@@bi-ome I have not watched it yet but will. Maybe that is the part I am missing 😅 thank you!
Hello! I´m using your solution, but I can just get the first 400 lines, and I have more than 500 items in planner. How can get those lines?
Turn on pagination in settings for the “list tasks” step and set the threshold above your total-
@@bi-ome That's what I thought, but it won't let me insert it... Thank you very much!
@@bi-ome I was doing something wrong, it accepted the value now... thanks again...
Hi i have problem with filtered bucket. I make everythink step by step with you but when I try to start flow I have information
InvalidTemplate. The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(item()?['bucketId'], items('For_each_task')?['bucketId'])' failed: 'The template language expression 'equals(item()?['bucketId'], items('For_each_task')?['bucketId'])' cannot be evaluated because property 'bucketId' cannot be selected. Property selection is not supported on values of type 'String'.
I check a lot of time and everywhere a choose option array
I'm also getting the same error
It sounds like you put the bucket info into a string variable, you need to make sure you're using an array-type variable. There's both a designation as a type in the dropdown menu when you initialize as well as the "append" step is specific - there's one for append to string and one for append to array. Make sure they're both array--
@@MrAstroteach It sounds like you put the bucket info into a string variable, you need to make sure you're using an array-type variable. There's both a designation as a type in the dropdown menu when you initialize as well as the "append" step is specific - there's one for append to string and one for append to array. Make sure they're both array--
@@bi-ome Apologies, I had buckets with nothing in them which caused the error! All user error rather than with the code. I'm just starting out on running queries in PowerAutomate so I've learned a lot as we've worked through. Thank you again!
@@MrAstroteach That's interesting, I didn't account for that and probably should have! There's always something 😅
I get an error message when I try to save. Says that I need to enter a valid json. Any ideas why?
This is under the Append array variable
Sounds like your JSON has a typo or something missing. There are JSON validation tools online, you could try pasting it in there and see if it will pinpoint the place where the issue is
I can't get the flow to work :( it keeps getting hung on the 'get task details' - doesn't fail, but I've tested it multiple times and it runs for hours.
When I go into the flow run details I can see that the step in the flow is pulling 102 'tasks' which is aligned with how many tasks are in the Plan. However, it won't progress passed this...
Try setting the concurrency control on the loop to 1? It might be getting throttled. And make sure it’s looping over the correct dynamic content card.
@@bi-ome that worked!!! thank you!!!
how to add created by and assigned to .
Assigned-to is in the tutorial - I call it "assignees", but it's assigned to😊
You could use the same technique we use for assignees on the "created by" user ID, you'd just need to add another array variable for that and insert it at the end in your final array.
i try with adding the array variable after the Buckets variable
then i follow same step apply to each assignees for created by and place below the assignees loop
and this two loop are below the get task details
after running the flow
iam getting this error
The error you're encountering in Power Automate is related to the fact that the expression @items('Apply_to_each_Created_by')?['createdBy'] is expected to be an array, but the result is of type 'Object'. To resolve this issue, you need to make sure that the expression returns an array.
@@bi-ome
@@bi-ome yup
i have done with the same procedure firstly i inserted the another variable (Created by) variable below the bucket variable make the type to Array.
then inside the For Each Task Loop below the Assignees Loop i have made same flow just like assignees.
im and getting an error
The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@items('Apply_to_each_Created_by')?['createdBy']' is of type 'Object'. The result must be a valid array.
could you please explain the flow.
@@gaurangkadam7623 Oh you know what, you actually don't have to go through the whole looping exercise because created-by won't ever be multivalue. I totally spaced on that. All you need to do is in your for-each-task loop use the "get user profile" action on the created-by ID and then put whatever resulting fields from that action you want in your main data variable that collects it all. So you don't need to loop on created by :)
@@bi-ome Yeah this work i have provided get user profile created by , then append to array variable🙂
i have one more issue like my assignee is with multiple name, we want to show all the assignee name , but it is showing single name in power query editor.
could you explained me.
i had apply compose and join operator below for each assignees.