Updates prompted by comments: LOTS OF TASKS 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 something low, like 1, on the "get task details" loop settings to prevent throttling. This whole technique is not the way to go if you have 5000+ tasks or hundreds of plans - premium Planner and the Dataverse connector will be where you want to go for that. Here's how to get the premium Planner data: th-cam.com/video/F-RrxxUC_Q4/w-d-xo.html Part two, VISUALIZING this data can be found here: th-cam.com/video/eEmpzueZfqY/w-d-xo.htmlsi=QCKeWjjUyhAXVgJt COMPLETED BY USER If you need the completed-by user info, that is not in the UI but you can get it in the for-each-task loop with the expression items('Apply_to_each')?['completedBy']?['user']?['id']. This gets their user ID, for which you'd need to do the same thing we did with assignees to get their display name (there's a field for display name in the outputs, but it seems to always be null). PRIORITY This field is not in the dynamic content UI, you have to reference it by expression if you want it. I covered this in a later video in the series, but the expression used to get it is items('For_each_task')?['priority']. If you've named your loop something else, you may need to adjust the name - it references the loop name in the function. NEW UI For anyone in the new Planner UI, the hyperlink for the tasks for the dynamic links can be found using "copy link" while looking at a task in Planner. The URL has changed from the one in this video, but the old link will still go to the new UI. The new link format looks like this: planner.cloud.microsoft/webui/v1/plan/PLAN_ID_HERE/view/board/task/TASK_ID_HERE The plan ID is in there. If you need the group ID for anything (e.g. Power Automate will ask for it in certain circumstances), you can get this by copying the link to a group in Teams and taking it out of the URL there, or it's also in the URL for groups here: myaccount.microsoft.com/groups/groups-i-own (or the admin portal if you're an admin). There is a ?tid=GUID_HERE at the end of the links in the new UI - I believe this is the tenant ID, if you take that chunk off it'll auto-populate and work fine (or you can leave it in). PREMIUM PLANNER This video is for standard Planner. Premium Planner has a totally different technique, please see this video if you have premium: th-cam.com/video/F-RrxxUC_Q4/w-d-xo.html
Chris....I think you are reading my mind !!! I was following all your tutorial steps, and I was thinking about how to get planner data from many plans, when I see this new video released about 30 minutes ago... You are amazing....Thanks
Thank you very much!!!!!!!! I created power automate a huge part because of you!!! And it will help a lot not only me but my collegues and my county’s municipality, too!!!
Hello Christine, This is amazing! Your entire contributions on this platform are very useful and very easy to follow by any class of learners. So far you, the best tutor I have come across on TH-cam. Thank you and keep up the good work!
Hi Christine, thank you for the video, everything worked perfectly!!!, except for the "Priority level" part you show in the last table. It's not being called from the Power Automate in the previous video. How can I add that information to my table?
Priority is in the list tasks output, but it's not in the UI, so you have to insert it by typing in the reference as an expression. So insert an expression in the data array that is items('For_each_task')?['priority']. If you've named your loop something else, you may need to adjust the name - it references the loop name in the function.
Yes, this is why we put assignees in their own related table. 🙂 Just use the name field on the Assignees table to filter instead of the calculated column. The calculated column is only there to show the names in a comma-separated way in a single table cell. We did both to get the best of both worlds.
ปีที่แล้ว +1
Hurray ✌ I guess it's what I was waiting for to implement all channels to one Power BI report. And also is it possible to show your Planner screen to understand better that; which data is transformming to PowerBI data at last.
Yeah, I was planning on doing a follow up on visualization with the Bacci Gantt chart - I will put in my notes to show the board on that one. I filled in more data in a plan for it, these were all pretend data so they’re kind of sparse lol.
Hi, thanks for all you videos. These are very help full. For the flow is working perfectly fine when I run this on 2 planners. But if I have more than 2 the assignees are mixed up. Some people from other planners are visible and assigned to tasks in other planners. Can please tell me where I might be going wrong. Thanks for the help in advance
It sounds like you missed the step that resets the assignee variable between loops. If you don't do that, it'll keep tacking them on as it loops. I re-recorded the "part 2" video of this series to include that step, so if you saw an earlier version you might go back and check it out again. :)
Hello Christine, thank you so much for these videos, your work is truly invaluable. I just finished this video, and followed all your steps, however on my SharePoint site the JSON file is returning the value of "[ ]" which I am assuming is empty. Do you perhaps have any idea what could be the issue? I am sorry if this is vague, I am still very new to this :)
Thanks! You would need to go into the flow run history - that will let you click through the results of each step. That will narrow down where the issue is. It could be a lot of things, like looping on the wrong field, it could be the append step, or it could be the step that creates the file. Check to make sure the array actually has data in it in the history and go from there. :)
@@bi-ome Hi again Christine, sorry to trouble you. So I managed to get all my plans into one flow but my challenge now is displaying each individual plan's name/title. Any suggestions?
Thanks Christine, super helpful video, would you know if its possible to show what linked Plans are being used in what Channels in Teams? As you know a Plan can be linked to any Channel you want so a Plan could be referenced in many Channels. Thanks for your response.
Do you mean when it's added as a tab in a channel? I don't think so. If it were anywhere, it'd be in the Teams reporting (learn.microsoft.com/en-us/microsoftteams/teams-analytics-and-reports/teams-reporting-reference) and I don't see it there.
Hello Christine! Thank you for this video! Is it possible to extract data from all groups and their plans in my organization? I've been assigned the Microsoft365 Administrator role and in the Teams admin center I can see all the groups in our organization, but I'm not a member of many of the groups. I have a task to build a single dashboard for tasks in Planner - for all groups, tasks, and so on. Please tell me how this can be done?
It’s possible, but will work a LOT better with premium Planner compared to basic. Reason being, you can connect directly to the tables in Power BI with the Dataverse connector in premium, and as an admin you’ll be able to see all data in the environment with no extra work (I have a video on this too). Premium Planner is really the route Microsoft is going for enterprise-level reporting, so just keep that in mind. There are some downsides to shifting to premium, too, though, so for example if you use a lot of automation in your plans to create tasks etc I would consider your options more carefully. With basic Planner and Power Automate you will hit the limits of number of API calls per day if you have a lot of plans (that’s why this video is more geared toward individual users/teams). If you are really committed to basic Planner, you might try using Logic Apps to make the flow instead - it’s priced on usage and so doesn’t have the same limits as Power Automate. For permissions on the basic Planner data, I think you’ll need to be a group member or owner to pull it, even if you’re an admin (the only other route is service principals, and I am not aware of that being something that works with basic Planner, but I haven’t looked too deeply there either). You can use PowerShell to add an account to all groups; I would recommend making a designated account for this and not using your user account. That will make it way more sustainable later if you change roles or leave your org.
Your videos are so helpful! I work as a project manager and need to monitor many tasks that aren't assigned to me across various plans, so this was a life-changer! The only issue is that my flow runs FOREVER because I am a member/owner of so many plans (60+). Do you have any tips to improve this?
Hey Christine, Thanks for the amazing video, i ran into a problem i couldn't figure out. 'For each plan' loop after the 'List plans for a group', it gives this error 'ExpressionEvaluationFailed. The execution of template action 'For_each_plan' failed: the result of the evaluation of 'foreach' expression ' @{outputs('List_plans_for_a_group')?['body/value']}' is of type 'String'. The result must be a valid array.' even though it returns in an array. I have 17 group ids and there are no blank values. Thanks!
@@saadsajjad1871 There should be a loop for each group and for each plan in the group, nested. Are there multiple plans in that one group that it’s getting stuck on? I didn’t test this on a group with only one plan, I don’t think, it’s possible that if it only has one it doesn’t want to loop on it. If that ends up being what it is, you could count the values and only loop if there is more than one? It’s hard to tell. You might also try the other variant of this video that gets plan data from a list of plans instead of getting everything - that is looping over a specific list so it should work for you.
Thanks for doing this! So helpful! I am going over your series and all is very helpful. I had one question: Is there a way to get the Project Task History data too? I have plan 3 for my organization and I think the Task History would be useful to show when hovering over certain parts of the dashboard.
Thanks! This wouldn't be super feasible to do... you don't have access to the history, so you'd have to store it yourself, and there's no trigger in Power Automate for when a task is modified so you'd essentially have to take snapshots on a schedule and then figure out the differences between snapshots in your PBIX. Not for the faint of heart for something that is more in the "nice to have" category. But technically with enough jank, most things are possible?
@@bi-ome You're right! I have access to the history but too much unnecessary data is created. As far as I researched, the best option is to review the activity log history to check changes. Thanks again for the resources and keep up the great job!
@@paulo.valverde Did you ask on Reddit? I think I am the one that responded there if you did lmao. This video series assumes you have non-premium Planner. If you have premium, you don't have to go through all this junk with the flows - the tables are just in Dataverse, and you can connect to them directly with Power BI with the Dataverse connector. When you do that, it lets you scroll through the list of tables (they will be in the default environment) - one of them has the tasks, there's another that has the assignments, and another has SOME history (not a ton). I touched on it really briefly at the end of this video: th-cam.com/video/PhTwhN7m6Lw/w-d-xo.html - it's on my shortlist to do another with more detail but I've not been getting to it. Honestly the audit log route is kind of iffy with this - it would involve changing table and column settings as a sys admin with no option of a dev environment, unless you try it in a separate tenant first. I would check the out of box history table first and see if that works...
@@bi-ome That was me asking on Reddit, yes! Thanks for your patience 😂 I’m still new to Power BI (coming from Tableau) and Planner so any type of information is super helpful. I did watch the video and it helped me understand where in the Dataverse was the data, and watched your series to understand it more and for inspiration! I was able to connect to Project History but honestly, there are so many columns and data that deciding which ones are specifically useful would take a lot so I’m not going to use it for now lol
Hi Christine, your last video brought me here. Yet another fantastic video! Thanks for sharing! In my flow I want ALL plans from one group, the flow seen to be working fine till it gets to 'For each plan' loop. It doesn't tell me what's wrong with it, the flow run time just timed out. I'm wondering if there's a max data threshold, or something else?
How many tasks are you working with? You can set the degrees of parallelism on the loops to 1, which conversely can speed things up if you have a lot of tasks because it can prevent you from hitting the throttling limits. The various services have thresholds on how many times they allow you to call the API in a set amount of time, which seems to be what people bump into most often.
@@bi-ome Hi! this timeout error is also happening to me! I have around 200 tasks I am trying to enter in, I have raised the degrees of parallelism to 15 - anything else I can do?
@@marycorina8654 You want the degrees of parallelism to be 1, actually - so set it to that, and wait a while before trying again. What happens is you're exceeding your allotted number of calls in a timeslot - you're actually pulling too fast, and getting throttled to next to nothing. What you want to do is intentionally slow the loops down to pace it a bit. It'll still take a while to run, but you shouldn't be running it more than once or twice a day anyway so it's alright.
Hi christine, like others I’m wanting to extract the comments from a planner task. I found in the get task action body outputs There is a value called conversation thread ID. I am new to Power Automate so because it’s not one of the dynamic value parameters I don’t know how to pull that value from just the body output. If you could help with that part I found there’s an office 365 group email connector that you could use action “get conversation thread” and if you input that conversation thread ID to that action it works great. I’ve done it by manually inputting that conversation thread ID. Just not sure how to pull that ID from the body and store it in a variable?
Where are you seeing a conversation ID? I went in and added comments to a task to test and I don't see it in the outputs of the list tasks or "get task details" steps. I do think I remember seeing it in premium, but on the Dataverse side, are you on premium Planner? That would be pretty cool if it works - good job tracking it down! You can reference outputs by using the step name, so if you're getting it from a for-each loop on tasks, you would use an inserted expression like "items('loop_name_here')?['conv_id_field_name_here']". The spaces in an action name always need to be replaced with underscores. I have a blog post about how to reference them here: christine-payton.com/parse-extract-json-power-automate/
@@bi-omeI am using the “Get a Task” action which I don’t believe I saw used in your video. It appears to be very similar to “List Tasks” just for a singular task.
@@MattBerry-ec3yx Oh interesting! I see it in that one, I expected it to have the same fields as list tasks, but it doesn't. If you're using "Get a task", you'd want to reference it with "outputs('Get_a_task')?['body']?['conversationThreadId']" as an expression to get the conv thread ID - I tested it to make sure I had the syntax right and it worked for me. Thanks for sharing!
Hi Chrisitine , First of all I should thank you for this amazing videos which is really helpful tried so many methods but yours worked perfectly. Only issue I am facing is subtasks are not getting retrieved and its empty in the output . Any thoughts on it
Make sure you’re grabbing the right dynamic content card for it, off the top of my head I think there are a few for subtasks that are really similarly named. For whichever output you’re looking at, scroll up the page to the step that gets the bulk of the data and make sure it actually has a value there too-
@@bi-ome Sure , also is there a way to get tasks under subchannels for example I have channel A - It haas Tasks T1,...n and inside that channel i have another one sub channel "Channel Sub" -i have task inside this too T1,...Tn .. in this case which action i should use to export all task inside subchannel
So excited that I got this to work - but I do have a question. I'm working with a number of plans that were shared with Private Teams Groups - - and those aren't showing up in the PBI data. Do you know if there's a way to also grab the private Teams groups/plans? I might have missed it if you already mentioned this somewhere... I watched a lot of your videos in one go :-)
Yeah, you can get plans in various ways, this one just happens to get the ones you are an “owner” of. It’ll get private plans you own, but if you don’t own the plans there’s another video in this playlist that will get a “list” of plans instead that should work (you just have to put the plan IDs you want in a list). 🙂
@@bi-ome Thanks, I had success with that one as well! Do you have any suggestions on how to export the full list of plans and plan ids out of Planner and perhaps into a Sharepoint list? I'm working with a group that has lots of plans in lots of places that I'm not a owner or member of
@@CarolineEcholocity You won’t be able to pull data for plans you’re not at least a member of, because you won’t have permission on them. There’s a “list groups” and “list plans for a group” actions, but if you’re trying to pull lots and lots of plans you’re going to hit all sorts of limits trying to get all the task data this way. Premium Planner is a better way to go for org-level Planner reporting because you can just connect directly to the database it’s stored in with Power BI and bypass all the shenanigans. The Power Automate route here is more of a “get my data” and “get my team’s” data sort of thing.
This is a really great video and was extremely helpful. How could I get tasks that are outside of groups though. For instance if I take meeting minutes within a Teams meeting using Loop. The task list is powered by Planner, however they do not show up in a group to my knowledge. My end goal is that I would like to take Meeting Minutes within a meeting associated with a project and have those tasks be automatically transferred to a plan within a group or SharePoint site. Is this possible?
Thanks! The loop tasks do actually show up in Planner and create a group. If you go to Planner they tend to show up in recent there with a Loop icon. If you open one it's got a group ID and plan ID in it, so I think it's creating a group on the back end. :x
@@bi-ome Thanks for the reply. I noticed that also; however, I tried this method that you showed and they don't seem to show up in the group list or plan list. I haven't tried to just get them through my tasks instead of all tasks, but that wouldn't really fulfill my needs anyway. I have decided to try to go the Microsoft Graph API and HTTP route instead. My situation seems like it would be a pretty common situation with pretty any project, so I am surprised MS doesn't have a standard solution to seamlessly link their software. Again, great video series.
Hello Chris !!!! Thank you for the time and effort you invested in creating and sharing this video. Appreciated 👏👏👏 My Test Flow is running for last 2 days i don't know why it is taking more time - Please advice Can i select only required planners - Please advice
If you have an excessive number of plans and only want to get a few, just put their plan IDs in a list [“plan1”,”plan2” etc] and have the “apply to each plan” loop over the smaller list of plans you actually want. Make your list of plans in a compose action.
@@bi-ome Hi Chris, video was really helpful and I was fine till the flow timed out because of too many groups. I am really having some dificulty working out how to apply the work around. Would you be able to list the steps. I would be greatly appricated, thank you.
Hi Christine, Thank you so much for the video! I just have an issue: List my owned groups (V2) get data from Teams instead of planner. So I have all the Teams channel I owned, but couldn't get any planner information... Do you know how I can fix this?
Teams and Planner plans are both associated with M365 groups - what this does is gets all the groups you own, then all the plans in those groups. If you're not the group owner for the group the plan is a part of, you would want to use a different action there. I have another video where we take this flow and instead of getting all the groups we own, we get specific groups that we know have the plans we want, which will work even if you're not the group owner. You might try this instead - no wasted effort either, since we just modify the flow you already made: th-cam.com/video/1UfxETalb9E/w-d-xo.html
This is just an amazing video! I managed to make a flow like a pro, however, it takes more than 4 hours to finish (it's not done yet) and it's because there are a bunch of groups and a bunch of tasks under those groups/plans. How and where do I add a condition to export only tasks assigned to specific users (their emails). The rest should be ignored and not pulled to Power BI. Please help :( I tried doing it myself, but I have no idea what I am doing. I feel it's simple but don't know where/how to start and where to put that step.
If you have a lot of tasks and plans, try setting degrees of parallelism on the loops to 1, wait some time for your throttle flag to drop (maybe a day? I don’t know how long it takes), and re-run. What happens is it tries to pull too much too fast and you get throttled by the service, so slowing it down can actually speed it up. The only place to filter these would be a “filter array” on the “list tasks” output (there’s no filtering in the “get”, though I suppose you could do a Graph API call instead of List Tasks, that may have more filtering options), then you’d loop over that filtered array instead of all tasks. It’s the apply to each loops that are slow, so that should help, but it’ll be somewhat tricky to filter on assignee because that’s a multivalue field, and you would have to use the user ID in the filter. It’d be easier to filter on created date if that’s an option, maybe get everything created in the last year. Alternatively, all this is a lot easier with premium Planner, because you don’t have to use Power Automate at all.
This was super useful! How about creating a time tracking tool based on this data. Is that something you could work on in the future? The general idea would be to charge time to planners or tasks as needed. Thank you again!
My hope is that the new Planner update they're releasing sometime this year will have those things, you never know! That is a really good idea though, what I would probably do is make a second flow that instead of outputting JSON just creates the task rows in a Dataverse table or SP list with the name and ID (no details). Then have another table/list that looks up to that and has timesheet records. Then you can pull that into the Power BI report, relating on task id. You'd probably need a canvas app to tie it all together so people could enter the data. I wish they would let you add custom fields on Planner tasks, that would be easier lol.
hello christine! thank you for the well done guide. I'm having some trouble, because in my case there are so many groups within my company. We have a specific description for those I'm interrested. Could you help in showing a way to filter the results to specific description or some other tag?
Sure, you can use the HTTP connector to call the Graph API to get groups following a pattern, like title contains or similar. There's documentation on it here, you could loop over the results of this instead of what we used in the video: learn.microsoft.com/en-us/graph/api/group-list?view=graph-rest-1.0&tabs=http#example-5-use-search-to-get-groups-with-display-names-that-contain-the-letters-video-or-a-description-that-contains-the-letters-prod-including-a-count-of-returned-objects
Thank you for this amazing video. My planner has more than 5000 plan and adding more to that. Can you tell me where to iterate to get the amount of row plan it has in the planner? please and thank you.
There’s not a great way to get over 5k with this. The threshold of tasks you can even put in a plan is right around there, so you might consider splitting the content out a bit and/or trying the new premium Planner (that one you only need licenses for people creating plans, and data is in Dataverse so you can just pull with Power BI and skip Power Automate).
@@BraveWilson I touch on it very briefly at the end of this video (th-cam.com/video/PhTwhN7m6Lw/w-d-xo.html), but haven't done an in-depth one yet. It's at the top of my list but I've been tight on time lately.
These videos have been super helpful. I have a question though. In this video you added all of the info one .json file, but then you still had the separate tables in PowerBI, or should I not have deleted those other Compose/Create File actions from my flow?
Sure, all the tables in Power BI were created from the single JSON file. They're basically dimensional queries of the same data. You don't need one file per table. :)
Although I should mention that if you followed one of the older tutorials, you might have multiple files - I re-recorded the video to streamline to a single file and add some things, so you might want to adjust if that was the case-- the process is a little different!
Your videos are incredibly good! Unfortunately, I am currently only using the free version and the flow causes 50,000 PPR (28 plans). This causes a runtime error. Do you have any tips on how to reduce the PPR or find a compromise solution?
It depends on if you need all 28. If you only need some of those, you can loop on a list of plans - I have a video for that in the project/task playlist in my channel. Power Automate premium licensing will give you more speed/higher volume allowances. If you really need a TON of Planner data, you might want to move to Planner Premium (for the Web) - that'll let you just connect to the raw Dataverse tables. I have a video for that too, and one for how to copy plans over. Note it doesn't have recurring tasks, not sure if that's a requirement.
@@bi-ome Thanks for the quick reply! I'll try to reduce the number of plans. Many tasks also have the status completed, which are not relevant. Do you think the approach of only filtering the tasks that have not the status completed can also significantly reduce the PPR?
@@Gonca-hc6ib Yeah, that would help - there's no filter on "list tasks", but you could use "filter array" on the output of that and then only get details for the ones with a % complete less than 100. Just make sure to put the filtered array data in the final data array, not the original list tasks data. You could also use Logic Apps, Logic Apps is more enterprise-scale and won't throttle you the same way because it's billed on usage per action instead of per user.
Hi, I hope everything is ok. My flow is triggered 4 times every day, I made the modifications that you indicated, but in a day for example 2 times it takes about 40minutes, but in the rest it takes 8 to 10 hours, why I could do or what else I could modify? now I have 780 tasks.
You are limited on the number of actions you can perform in Power Automate over a period of time. So it makes sense that the first couple were faster and then you were throttled. I don't recommend running this more than once per day. If you REALLY want it more than that, you can get a premium Power Automate license which has higher limits--
It depends how large your organization is. If you have more than 5000 total tasks, you'd really want to start looking at Logic Apps or premium Planner instead - premium Planner data is stored in Dataverse tables that you can create a view-all role for to pull everything without Power Automate (just use the Dataverse connector - I have a video on that too). You'll hit the API calls per day limit on Power Automate if you're pulling at a large organizational scale. There's pros and cons to premium Planner, but it really is more scalable and is where Microsoft is pushing people for org-level reporting. Only project managers need licenses, so it's not heinously expensive.
@@bi-ome thank you for your reply. But if we have less than 5000 tasks, and not premium planner, is it achievable in the way which you do report in this video? First thing, which come in mind is to add user under which flow is working to all groups to see this plans. But maybe there is a better way
@ Yes, if you have less than 5k tasks, you could add a service account (this is a licensed account not for a specific person) to the groups as a member or owner and use the “get my owned groups” or the equivalent for groups it is a member of. It might be possible to use a service principal as well, but I’ve not looked into that as much-
Hello Christine. Thank you for all your helpful tutorials. Our Planner has a lot of tasks thus I have a question regarding how you set the degrees of parallelism on the loops to 1. We continue to receive an email when our flow runs saying: "Your flow is running too many actions...Your flow has exceeded its Power Platform Requests limit in the past 24 hours. If this high usage persists, your flow actions may get throttled and slowed down. Power Platform Requests used: 13119, Power Platform Requests transition limit: 10000." Would this help fix our issue?
You can slow the number of requests down by setting the degrees of parallelism on the loops to 1, but it is going to take a loooong time to run. If you are using Planner that heavily, you might consider swapping to premium Planner - premium stores the records in Dataverse, so you can get them without using Power Automate at all (just connect to the tables with the Dataverse connector). Premium Planner isn't all that expensive, and only the project managers need licenses (regular users can still add/update tasks without).
Thanks for this tutorial ! I've got a problem, The flow say that it worked fine but JSOn file size drop to 1Ko from 175 Ko, when I want to reload the json, it says : error can't find column1 and it says table empty. Do you have any idea? I've done exactly what you've done, minus the flag step....
What's actually in the JSON if you open it? You can also look at the flow history and click through the steps to try to narrow down where the issue is. Each step has an output value, so you can see what it's sending to the next step and whether or not it's getting data or the right data.
Nevermind, just found out that I don't own any group xD I have make my way around with " List all my groups and belong to" but damn it took 5 h to run :o
@@anthonylozach7457 🤣If you get the plan IDs of the ones you want and put them in an array variable, you can probably loop over that instead. You can get the plan IDs from the URL when you're viewing a plan--
Hello Christine, thank you for all your helpful tutorials. I have a question, how can I get the name of the person who completed the task? Especially when the task is assigned to multiple persons I want to see in the power bi report who completed the task. Thank you!
That info isn't in the outputs for the list tasks or get task details steps, it only has created by, assigned by, and assigned-to user IDs. I don't see it in the API documentation, either, so I don't think it's accessible to us.
@@bi-ome I saw in one of your videos that you posted that the value for 'completed by user display name' appeared, but it was filled with null everywhere, and I was thinking that there might still be a possibility. This was the video th-cam.com/video/RbcrJbwoIOw/w-d-xo.html
@@DariaOrlea Oh you know what, you're right - this is another one of those that doesn't show up at all in the UI. I was doing a search on outputs and didn't see it, I think because the plan I was looking at didn't have any completed tasks? I was able to get the completed-by user ID with the expression items('Apply_to_each')?['completedBy']?['user']?['id'] - you can feed that to the get user profile to get their name/email. Their display name seems to always be null.
@@bi-ome Could you send a picture of the changes you've made, since i'm also interested in the 'completed by' part for a report, so i can keep track? I'm still new to Power Automate and didn’t quite understand what to modify
@@lucavlad5669 I haven't made a flow modification for it, I will put it on my list to do. I just fiddled with a compose action until I got the ID reference right. It'd be handled similar to the assignees, but without a loop since there's only one value.
Hi Christine, I have followed all your steps but getting a time out issue when I test it. I also received an email saying my flow is running too many actions and that it has exceeded its Power Platform Requests limit in the past 24 hours. How can I avoid this?
Hello! If you have more than 500 or so tasks, you will want to turn on pagination in the "list tasks" settings in the ellipsis menu and set the degrees of parallelism on the loops to 1. This will get all the tasks and avoid throttling errors. But, if you have a TON of groups, you might consider only getting the specific plans you need instead - I did a follow-up video on that here: th-cam.com/video/1UfxETalb9E/w-d-xo.html
@@bi-ome Thanks so much for your quick reply! I'll take a look at that video. I have about 20 channels, each with 1 planner list which can have anywhere between 5-200 tasks in it.
I have tried the Workflow. It worked when I created it for a specific plan but when amended for all plans I get the following error. Are you able to advise how I can resolve please?: Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'For_each_Bucket' at line '1 and column '3667' cannot reference action 'List_buckets'. The action 'List_buckets' is nested in a foreach scope of multiple levels. Referencing repetition actions from outside the scope is supported only when there are no multiple levels of nesting.'.'.
Nice video ! I've a question. Is there a way to generate the .json file only extracting information from an Specific Bucket? Let's say I have an Operations Planner and I have these buckets: Operations, Maintenance, Safety, Manufacturing, and I just need to get a .json with Operations tasks? I'm a newby on Power Automate :( Thanks in advice, new subscriber !
Yeah, normally you'd just put a filter on the first query that gets the tasks, but it looks like "list tasks" doesn't have a box to add a filter like other connectors do... so what you could do instead is use "filter array" and filter on the bucket ID of the bucket you want after listing all tasks, then use that filtered array for your for-each loop getting the details. I wouldn't do this on the "get all my plans from all my groups" flow, I'd make flow for just the one plan here. Alternately, it's really easy to filter on the Power BI side in Power Query (like literally 2 seconds) once you have everything - so it depends on if you're filtering because you have so many tasks that the flow takes forever, or if you just don't need to see them all in the report. :)
@@bi-ome Finally I decided to filter directly from Power Query, that was just an idea 💡 I've tried creating different Flows for each bucket but, Power Query was the best option to. Thank you very much!!!
Hi Christine, this is easier than using microsoft graph API to get data, especially because no premium tasks are required, but, do you know how to get the comments history from a task?
Hi Christine, after using this flow for a while I get the following error: Your flow has exceeded its Power Platform Requests. Have you experienced this ?
It sounds like you're getting throttled - you can try setting the parallelism on the loops to 1 to slow things down. It's based on how many calls you do in a period of time. Premium Power Automate licenses also have a higher allowance and I think faster speed.
@@bi-ome I'm also running into an issue of a similar flow taking somewhere around 3 to 5 hours to complete. I know that's very dependent on how many groups, plans and tasks I have. This is for about 259 plans and close to 4000 tasks. I just wish it can go faster.
@@carlomendoza7246 If we didn't have to use a separate action to get all of the details for the tasks, this would be much faster! The problem is that the "list tasks" only has half of it, and apply to each loops are always slow. I would really recommend people who are super heavy Planner users look at the Planner Plan 1 / Planner Premium, because it's $10/month and only the project manager needs a license. That product stores the data in accessible tables, and you can pull it with the Dataverse connector vs doing all this hijinks (a video on this is next on my list, promise...). It also has more features you probably want anyways, like a functional Gantt chart. 😅
Good question, that's in the "References Resource Link" dynamic content card - it's a hyperlink to the attachment. Since there can be multiple, I'd make another array variable called AttachmentLinks and append them in a loop to the variable after "get task details" and then stick that variable in a property on the final data array. Not sure what you need to do with the attachment exactly, so hard to say after that but that gets things started. 🙂
Updates prompted by comments:
LOTS OF TASKS
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 something low, like 1, on the "get task details" loop settings to prevent throttling. This whole technique is not the way to go if you have 5000+ tasks or hundreds of plans - premium Planner and the Dataverse connector will be where you want to go for that. Here's how to get the premium Planner data: th-cam.com/video/F-RrxxUC_Q4/w-d-xo.html
Part two, VISUALIZING this data can be found here: th-cam.com/video/eEmpzueZfqY/w-d-xo.htmlsi=QCKeWjjUyhAXVgJt
COMPLETED BY USER
If you need the completed-by user info, that is not in the UI but you can get it in the for-each-task loop with the expression items('Apply_to_each')?['completedBy']?['user']?['id']. This gets their user ID, for which you'd need to do the same thing we did with assignees to get their display name (there's a field for display name in the outputs, but it seems to always be null).
PRIORITY
This field is not in the dynamic content UI, you have to reference it by expression if you want it. I covered this in a later video in the series, but the expression used to get it is items('For_each_task')?['priority']. If you've named your loop something else, you may need to adjust the name - it references the loop name in the function.
NEW UI
For anyone in the new Planner UI, the hyperlink for the tasks for the dynamic links can be found using "copy link" while looking at a task in Planner. The URL has changed from the one in this video, but the old link will still go to the new UI. The new link format looks like this:
planner.cloud.microsoft/webui/v1/plan/PLAN_ID_HERE/view/board/task/TASK_ID_HERE
The plan ID is in there. If you need the group ID for anything (e.g. Power Automate will ask for it in certain circumstances), you can get this by copying the link to a group in Teams and taking it out of the URL there, or it's also in the URL for groups here: myaccount.microsoft.com/groups/groups-i-own (or the admin portal if you're an admin).
There is a ?tid=GUID_HERE at the end of the links in the new UI - I believe this is the tenant ID, if you take that chunk off it'll auto-populate and work fine (or you can leave it in).
PREMIUM PLANNER
This video is for standard Planner. Premium Planner has a totally different technique, please see this video if you have premium:
th-cam.com/video/F-RrxxUC_Q4/w-d-xo.html
Thanks for explaining the WHOLE "Planner to PowerBI" process so clearly, completely and efficiently - including your blog. Great job, thanks!
@@businessinsights_AlexRobe Thank you!! 😄
Chris....I think you are reading my mind !!! I was following all your tutorial steps, and I was thinking about how to get planner data from many plans, when I see this new video released about 30 minutes ago... You are amazing....Thanks
😄
Thank you very much!!!!!!!! I created power automate a huge part because of you!!! And it will help a lot not only me but my collegues and my county’s municipality, too!!!
Aw that's so great!! Thanks so much for sharing with me :)
Hello Christine, This is amazing! Your entire contributions on this platform are very useful and very easy to follow by any class of learners. So far you, the best tutor I have come across on TH-cam. Thank you and keep up the good work!
Thank you so much for your kind words! You totally made my day. :D
Hi Christine, thank you for the video, everything worked perfectly!!!, except for the "Priority level" part you show in the last table. It's not being called from the Power Automate in the previous video. How can I add that information to my table?
Priority is in the list tasks output, but it's not in the UI, so you have to insert it by typing in the reference as an expression. So insert an expression in the data array that is items('For_each_task')?['priority']. If you've named your loop something else, you may need to adjust the name - it references the loop name in the function.
@@bi-ome Oh my goodness, Christine, you're a genius! Thank you so much! Now, I need to define the meaning for each priority number ☺
I already got it , Urgency 1,important 3, medium 5, low 9! 🤩
Hi Christine, wonderful video! Is it possible to filter for the name of only one assignees when the task have multiple names? Thank you
Yes, this is why we put assignees in their own related table. 🙂 Just use the name field on the Assignees table to filter instead of the calculated column. The calculated column is only there to show the names in a comma-separated way in a single table cell. We did both to get the best of both worlds.
Hurray ✌ I guess it's what I was waiting for to implement all channels to one Power BI report. And also is it possible to show your Planner screen to understand better that; which data is transformming to PowerBI data at last.
Yeah, I was planning on doing a follow up on visualization with the Bacci Gantt chart - I will put in my notes to show the board on that one. I filled in more data in a plan for it, these were all pretend data so they’re kind of sparse lol.
Hi, thanks for all you videos. These are very help full. For the flow is working perfectly fine when I run this on 2 planners. But if I have more than 2 the assignees are mixed up. Some people from other planners are visible and assigned to tasks in other planners. Can please tell me where I might be going wrong. Thanks for the help in advance
It sounds like you missed the step that resets the assignee variable between loops. If you don't do that, it'll keep tacking them on as it loops. I re-recorded the "part 2" video of this series to include that step, so if you saw an earlier version you might go back and check it out again. :)
Hello Christine, thank you so much for these videos, your work is truly invaluable. I just finished this video, and followed all your steps, however on my SharePoint site the JSON file is returning the value of "[ ]" which I am assuming is empty. Do you perhaps have any idea what could be the issue? I am sorry if this is vague, I am still very new to this :)
Thanks! You would need to go into the flow run history - that will let you click through the results of each step. That will narrow down where the issue is. It could be a lot of things, like looping on the wrong field, it could be the append step, or it could be the step that creates the file. Check to make sure the array actually has data in it in the history and go from there. :)
@@bi-ome Thank you so much for getting back to me, I will definitely go and do that! Keep up the fantastic work :)
@@bi-ome Hi again Christine, sorry to trouble you. So I managed to get all my plans into one flow but my challenge now is displaying each individual plan's name/title. Any suggestions?
Thanks Christine, super helpful video, would you know if its possible to show what linked Plans are being used in what Channels in Teams? As you know a Plan can be linked to any Channel you want so a Plan could be referenced in many Channels. Thanks for your response.
Do you mean when it's added as a tab in a channel? I don't think so. If it were anywhere, it'd be in the Teams reporting (learn.microsoft.com/en-us/microsoftteams/teams-analytics-and-reports/teams-reporting-reference) and I don't see it there.
Hello Christine! Thank you for this video! Is it possible to extract data from all groups and their plans in my organization? I've been assigned the Microsoft365 Administrator role and in the Teams admin center I can see all the groups in our organization, but I'm not a member of many of the groups. I have a task to build a single dashboard for tasks in Planner - for all groups, tasks, and so on. Please tell me how this can be done?
It’s possible, but will work a LOT better with premium Planner compared to basic. Reason being, you can connect directly to the tables in Power BI with the Dataverse connector in premium, and as an admin you’ll be able to see all data in the environment with no extra work (I have a video on this too). Premium Planner is really the route Microsoft is going for enterprise-level reporting, so just keep that in mind. There are some downsides to shifting to premium, too, though, so for example if you use a lot of automation in your plans to create tasks etc I would consider your options more carefully.
With basic Planner and Power Automate you will hit the limits of number of API calls per day if you have a lot of plans (that’s why this video is more geared toward individual users/teams). If you are really committed to basic Planner, you might try using Logic Apps to make the flow instead - it’s priced on usage and so doesn’t have the same limits as Power Automate. For permissions on the basic Planner data, I think you’ll need to be a group member or owner to pull it, even if you’re an admin (the only other route is service principals, and I am not aware of that being something that works with basic Planner, but I haven’t looked too deeply there either). You can use PowerShell to add an account to all groups; I would recommend making a designated account for this and not using your user account. That will make it way more sustainable later if you change roles or leave your org.
Your videos are so helpful! I work as a project manager and need to monitor many tasks that aren't assigned to me across various plans, so this was a life-changer! The only issue is that my flow runs FOREVER because I am a member/owner of so many plans (60+). Do you have any tips to improve this?
Thank you, this is great! Looking forward to seeing dashboards where MS planner data is visualised.
Hey Christine,
Thanks for the amazing video, i ran into a problem i couldn't figure out. 'For each plan' loop after the 'List plans for a group', it gives this error 'ExpressionEvaluationFailed. The execution of template action 'For_each_plan' failed: the result of the evaluation of 'foreach' expression ' @{outputs('List_plans_for_a_group')?['body/value']}' is of type 'String'. The result must be a valid array.' even though it returns in an array.
I have 17 group ids and there are no blank values. Thanks!
@@saadsajjad1871 There should be a loop for each group and for each plan in the group, nested. Are there multiple plans in that one group that it’s getting stuck on? I didn’t test this on a group with only one plan, I don’t think, it’s possible that if it only has one it doesn’t want to loop on it. If that ends up being what it is, you could count the values and only loop if there is more than one? It’s hard to tell. You might also try the other variant of this video that gets plan data from a list of plans instead of getting everything - that is looping over a specific list so it should work for you.
Thanks for doing this! So helpful! I am going over your series and all is very helpful. I had one question: Is there a way to get the Project Task History data too? I have plan 3 for my organization and I think the Task History would be useful to show when hovering over certain parts of the dashboard.
Thanks! This wouldn't be super feasible to do... you don't have access to the history, so you'd have to store it yourself, and there's no trigger in Power Automate for when a task is modified so you'd essentially have to take snapshots on a schedule and then figure out the differences between snapshots in your PBIX. Not for the faint of heart for something that is more in the "nice to have" category. But technically with enough jank, most things are possible?
@@bi-ome You're right! I have access to the history but too much unnecessary data is created. As far as I researched, the best option is to review the activity log history to check changes.
Thanks again for the resources and keep up the great job!
@@paulo.valverde Did you ask on Reddit? I think I am the one that responded there if you did lmao. This video series assumes you have non-premium Planner. If you have premium, you don't have to go through all this junk with the flows - the tables are just in Dataverse, and you can connect to them directly with Power BI with the Dataverse connector. When you do that, it lets you scroll through the list of tables (they will be in the default environment) - one of them has the tasks, there's another that has the assignments, and another has SOME history (not a ton). I touched on it really briefly at the end of this video: th-cam.com/video/PhTwhN7m6Lw/w-d-xo.html - it's on my shortlist to do another with more detail but I've not been getting to it.
Honestly the audit log route is kind of iffy with this - it would involve changing table and column settings as a sys admin with no option of a dev environment, unless you try it in a separate tenant first. I would check the out of box history table first and see if that works...
@@bi-ome That was me asking on Reddit, yes! Thanks for your patience 😂 I’m still new to Power BI (coming from Tableau) and Planner so any type of information is super helpful. I did watch the video and it helped me understand where in the Dataverse was the data, and watched your series to understand it more and for inspiration!
I was able to connect to Project History but honestly, there are so many columns and data that deciding which ones are specifically useful would take a lot so I’m not going to use it for now lol
Hi Christine, your last video brought me here. Yet another fantastic video! Thanks for sharing!
In my flow I want ALL plans from one group, the flow seen to be working fine till it gets to 'For each plan' loop. It doesn't tell me what's wrong with it, the flow run time just timed out. I'm wondering if there's a max data threshold, or something else?
How many tasks are you working with? You can set the degrees of parallelism on the loops to 1, which conversely can speed things up if you have a lot of tasks because it can prevent you from hitting the throttling limits. The various services have thresholds on how many times they allow you to call the API in a set amount of time, which seems to be what people bump into most often.
@@bi-ome Hi! this timeout error is also happening to me! I have around 200 tasks I am trying to enter in, I have raised the degrees of parallelism to 15 - anything else I can do?
@@marycorina8654 You want the degrees of parallelism to be 1, actually - so set it to that, and wait a while before trying again. What happens is you're exceeding your allotted number of calls in a timeslot - you're actually pulling too fast, and getting throttled to next to nothing. What you want to do is intentionally slow the loops down to pace it a bit. It'll still take a while to run, but you shouldn't be running it more than once or twice a day anyway so it's alright.
Hi christine, like others I’m wanting to extract the comments from a planner task. I found in the get task action body outputs There is a value called conversation thread ID. I am new to Power Automate so because it’s not one of the dynamic value parameters I don’t know how to pull that value from just the body output. If you could help with that part I found there’s an office 365 group email connector that you could use action “get conversation thread” and if you input that conversation thread ID to that action it works great. I’ve done it by manually inputting that conversation thread ID. Just not sure how to pull that ID from the body and store it in a variable?
Where are you seeing a conversation ID? I went in and added comments to a task to test and I don't see it in the outputs of the list tasks or "get task details" steps. I do think I remember seeing it in premium, but on the Dataverse side, are you on premium Planner?
That would be pretty cool if it works - good job tracking it down! You can reference outputs by using the step name, so if you're getting it from a for-each loop on tasks, you would use an inserted expression like "items('loop_name_here')?['conv_id_field_name_here']". The spaces in an action name always need to be replaced with underscores. I have a blog post about how to reference them here: christine-payton.com/parse-extract-json-power-automate/
@@bi-omeI am using the “Get a Task” action which I don’t believe I saw used in your video. It appears to be very similar to “List Tasks” just for a singular task.
Kr🎉 o
@@MattBerry-ec3yx Oh interesting! I see it in that one, I expected it to have the same fields as list tasks, but it doesn't. If you're using "Get a task", you'd want to reference it with "outputs('Get_a_task')?['body']?['conversationThreadId']" as an expression to get the conv thread ID - I tested it to make sure I had the syntax right and it worked for me.
Thanks for sharing!
Hi Chrisitine , First of all I should thank you for this amazing videos which is really helpful tried so many methods but yours worked perfectly. Only issue I am facing is subtasks are not getting retrieved and its empty in the output . Any thoughts on it
Make sure you’re grabbing the right dynamic content card for it, off the top of my head I think there are a few for subtasks that are really similarly named. For whichever output you’re looking at, scroll up the page to the step that gets the bulk of the data and make sure it actually has a value there too-
@@bi-ome Sure , also is there a way to get tasks under subchannels for example I have channel A - It haas Tasks T1,...n and inside that channel i have another one sub channel "Channel Sub" -i have task inside this too T1,...Tn .. in this case which action i should use to export all task inside subchannel
So excited that I got this to work - but I do have a question. I'm working with a number of plans that were shared with Private Teams Groups - - and those aren't showing up in the PBI data. Do you know if there's a way to also grab the private Teams groups/plans? I might have missed it if you already mentioned this somewhere... I watched a lot of your videos in one go :-)
Yeah, you can get plans in various ways, this one just happens to get the ones you are an “owner” of. It’ll get private plans you own, but if you don’t own the plans there’s another video in this playlist that will get a “list” of plans instead that should work (you just have to put the plan IDs you want in a list). 🙂
@@bi-ome Thanks, I had success with that one as well! Do you have any suggestions on how to export the full list of plans and plan ids out of Planner and perhaps into a Sharepoint list? I'm working with a group that has lots of plans in lots of places that I'm not a owner or member of
@@CarolineEcholocity You won’t be able to pull data for plans you’re not at least a member of, because you won’t have permission on them. There’s a “list groups” and “list plans for a group” actions, but if you’re trying to pull lots and lots of plans you’re going to hit all sorts of limits trying to get all the task data this way. Premium Planner is a better way to go for org-level Planner reporting because you can just connect directly to the database it’s stored in with Power BI and bypass all the shenanigans. The Power Automate route here is more of a “get my data” and “get my team’s” data sort of thing.
@@bi-ome Got it, thank you!
This is a really great video and was extremely helpful. How could I get tasks that are outside of groups though. For instance if I take meeting minutes within a Teams meeting using Loop. The task list is powered by Planner, however they do not show up in a group to my knowledge. My end goal is that I would like to take Meeting Minutes within a meeting associated with a project and have those tasks be automatically transferred to a plan within a group or SharePoint site. Is this possible?
Thanks! The loop tasks do actually show up in Planner and create a group. If you go to Planner they tend to show up in recent there with a Loop icon. If you open one it's got a group ID and plan ID in it, so I think it's creating a group on the back end. :x
@@bi-ome Thanks for the reply. I noticed that also; however, I tried this method that you showed and they don't seem to show up in the group list or plan list. I haven't tried to just get them through my tasks instead of all tasks, but that wouldn't really fulfill my needs anyway. I have decided to try to go the Microsoft Graph API and HTTP route instead. My situation seems like it would be a pretty common situation with pretty any project, so I am surprised MS doesn't have a standard solution to seamlessly link their software. Again, great video series.
Hello Chris !!!! Thank you for the time and effort you invested in creating and sharing this video. Appreciated 👏👏👏
My Test Flow is running for last 2 days i don't know why it is taking more time - Please advice
Can i select only required planners - Please advice
If you have an excessive number of plans and only want to get a few, just put their plan IDs in a list [“plan1”,”plan2” etc] and have the “apply to each plan” loop over the smaller list of plans you actually want. Make your list of plans in a compose action.
@@bi-ome Hi Chris, video was really helpful and I was fine till the flow timed out because of too many groups.
I am really having some dificulty working out how to apply the work around.
Would you be able to list the steps.
I would be greatly appricated, thank you.
Hi Christine, Thank you so much for the video! I just have an issue: List my owned groups (V2) get data from Teams instead of planner. So I have all the Teams channel I owned, but couldn't get any planner information... Do you know how I can fix this?
Teams and Planner plans are both associated with M365 groups - what this does is gets all the groups you own, then all the plans in those groups. If you're not the group owner for the group the plan is a part of, you would want to use a different action there. I have another video where we take this flow and instead of getting all the groups we own, we get specific groups that we know have the plans we want, which will work even if you're not the group owner. You might try this instead - no wasted effort either, since we just modify the flow you already made: th-cam.com/video/1UfxETalb9E/w-d-xo.html
This is just an amazing video! I managed to make a flow like a pro, however, it takes more than 4 hours to finish (it's not done yet) and it's because there are a bunch of groups and a bunch of tasks under those groups/plans.
How and where do I add a condition to export only tasks assigned to specific users (their emails). The rest should be ignored and not pulled to Power BI. Please help :( I tried doing it myself, but I have no idea what I am doing. I feel it's simple but don't know where/how to start and where to put that step.
If you have a lot of tasks and plans, try setting degrees of parallelism on the loops to 1, wait some time for your throttle flag to drop (maybe a day? I don’t know how long it takes), and re-run. What happens is it tries to pull too much too fast and you get throttled by the service, so slowing it down can actually speed it up. The only place to filter these would be a “filter array” on the “list tasks” output (there’s no filtering in the “get”, though I suppose you could do a Graph API call instead of List Tasks, that may have more filtering options), then you’d loop over that filtered array instead of all tasks. It’s the apply to each loops that are slow, so that should help, but it’ll be somewhat tricky to filter on assignee because that’s a multivalue field, and you would have to use the user ID in the filter. It’d be easier to filter on created date if that’s an option, maybe get everything created in the last year.
Alternatively, all this is a lot easier with premium Planner, because you don’t have to use Power Automate at all.
This was super useful! How about creating a time tracking tool based on this data. Is that something you could work on in the future? The general idea would be to charge time to planners or tasks as needed. Thank you again!
My hope is that the new Planner update they're releasing sometime this year will have those things, you never know! That is a really good idea though, what I would probably do is make a second flow that instead of outputting JSON just creates the task rows in a Dataverse table or SP list with the name and ID (no details). Then have another table/list that looks up to that and has timesheet records. Then you can pull that into the Power BI report, relating on task id. You'd probably need a canvas app to tie it all together so people could enter the data. I wish they would let you add custom fields on Planner tasks, that would be easier lol.
Exactly! you are amazing!@@bi-ome
hello christine! thank you for the well done guide. I'm having some trouble, because in my case there are so many groups within my company. We have a specific description for those I'm interrested. Could you help in showing a way to filter the results to specific description or some other tag?
Sure, you can use the HTTP connector to call the Graph API to get groups following a pattern, like title contains or similar. There's documentation on it here, you could loop over the results of this instead of what we used in the video: learn.microsoft.com/en-us/graph/api/group-list?view=graph-rest-1.0&tabs=http#example-5-use-search-to-get-groups-with-display-names-that-contain-the-letters-video-or-a-description-that-contains-the-letters-prod-including-a-count-of-returned-objects
Thank you for this amazing video. My planner has more than 5000 plan and adding more to that. Can you tell me where to iterate to get the amount of row plan it has in the planner? please and thank you.
There’s not a great way to get over 5k with this. The threshold of tasks you can even put in a plan is right around there, so you might consider splitting the content out a bit and/or trying the new premium Planner (that one you only need licenses for people creating plans, and data is in Dataverse so you can just pull with Power BI and skip Power Automate).
@@bi-ome Do you have any video on how to use the Dataverse for pulling data from the planner without using Power Automate?
@@BraveWilson I touch on it very briefly at the end of this video (th-cam.com/video/PhTwhN7m6Lw/w-d-xo.html), but haven't done an in-depth one yet. It's at the top of my list but I've been tight on time lately.
These videos have been super helpful. I have a question though. In this video you added all of the info one .json file, but then you still had the separate tables in PowerBI, or should I not have deleted those other Compose/Create File actions from my flow?
Sure, all the tables in Power BI were created from the single JSON file. They're basically dimensional queries of the same data. You don't need one file per table. :)
Although I should mention that if you followed one of the older tutorials, you might have multiple files - I re-recorded the video to streamline to a single file and add some things, so you might want to adjust if that was the case-- the process is a little different!
Your videos are incredibly good! Unfortunately, I am currently only using the free version and the flow causes 50,000 PPR (28 plans). This causes a runtime error. Do you have any tips on how to reduce the PPR or find a compromise solution?
It depends on if you need all 28. If you only need some of those, you can loop on a list of plans - I have a video for that in the project/task playlist in my channel. Power Automate premium licensing will give you more speed/higher volume allowances. If you really need a TON of Planner data, you might want to move to Planner Premium (for the Web) - that'll let you just connect to the raw Dataverse tables. I have a video for that too, and one for how to copy plans over. Note it doesn't have recurring tasks, not sure if that's a requirement.
@@bi-ome Thanks for the quick reply! I'll try to reduce the number of plans. Many tasks also have the status completed, which are not relevant. Do you think the approach of only filtering the tasks that have not the status completed can also significantly reduce the PPR?
@@Gonca-hc6ib Yeah, that would help - there's no filter on "list tasks", but you could use "filter array" on the output of that and then only get details for the ones with a % complete less than 100. Just make sure to put the filtered array data in the final data array, not the original list tasks data. You could also use Logic Apps, Logic Apps is more enterprise-scale and won't throttle you the same way because it's billed on usage per action instead of per user.
Hi, I hope everything is ok. My flow is triggered 4 times every day, I made the modifications that you indicated, but in a day for example 2 times it takes about 40minutes, but in the rest it takes 8 to 10 hours, why I could do or what else I could modify? now I have 780 tasks.
You are limited on the number of actions you can perform in Power Automate over a period of time. So it makes sense that the first couple were faster and then you were throttled. I don't recommend running this more than once per day. If you REALLY want it more than that, you can get a premium Power Automate license which has higher limits--
is it any way to build this report for entire organisation(iterate through all teams with planner), to provide this report to management?
It depends how large your organization is. If you have more than 5000 total tasks, you'd really want to start looking at Logic Apps or premium Planner instead - premium Planner data is stored in Dataverse tables that you can create a view-all role for to pull everything without Power Automate (just use the Dataverse connector - I have a video on that too). You'll hit the API calls per day limit on Power Automate if you're pulling at a large organizational scale.
There's pros and cons to premium Planner, but it really is more scalable and is where Microsoft is pushing people for org-level reporting. Only project managers need licenses, so it's not heinously expensive.
@@bi-ome thank you for your reply. But if we have less than 5000 tasks, and not premium planner, is it achievable in the way which you do report in this video? First thing, which come in mind is to add user under which flow is working to all groups to see this plans. But maybe there is a better way
@ Yes, if you have less than 5k tasks, you could add a service account (this is a licensed account not for a specific person) to the groups as a member or owner and use the “get my owned groups” or the equivalent for groups it is a member of. It might be possible to use a service principal as well, but I’ve not looked into that as much-
Hello Christine. Thank you for all your helpful tutorials. Our Planner has a lot of tasks thus I have a question regarding how you set the degrees of parallelism on the loops to 1. We continue to receive an email when our flow runs saying: "Your flow is running too many actions...Your flow has exceeded its Power Platform Requests limit in the past 24 hours. If this high usage persists, your flow actions may get throttled and slowed down. Power Platform Requests used: 13119, Power Platform Requests transition limit: 10000." Would this help fix our issue?
You can slow the number of requests down by setting the degrees of parallelism on the loops to 1, but it is going to take a loooong time to run. If you are using Planner that heavily, you might consider swapping to premium Planner - premium stores the records in Dataverse, so you can get them without using Power Automate at all (just connect to the tables with the Dataverse connector). Premium Planner isn't all that expensive, and only the project managers need licenses (regular users can still add/update tasks without).
Thanks for this tutorial ! I've got a problem, The flow say that it worked fine but JSOn file size drop to 1Ko from 175 Ko, when I want to reload the json, it says : error can't find column1 and it says table empty. Do you have any idea? I've done exactly what you've done, minus the flag step....
What's actually in the JSON if you open it? You can also look at the flow history and click through the steps to try to narrow down where the issue is. Each step has an output value, so you can see what it's sending to the next step and whether or not it's getting data or the right data.
Nevermind, just found out that I don't own any group xD I have make my way around with " List all my groups and belong to" but damn it took 5 h to run :o
@@anthonylozach7457 🤣If you get the plan IDs of the ones you want and put them in an array variable, you can probably loop over that instead. You can get the plan IDs from the URL when you're viewing a plan--
This video is amazing, thank you so much.
Hello Christine, thank you for all your helpful tutorials. I have a question, how can I get the name of the person who completed the task? Especially when the task is assigned to multiple persons I want to see in the power bi report who completed the task. Thank you!
That info isn't in the outputs for the list tasks or get task details steps, it only has created by, assigned by, and assigned-to user IDs. I don't see it in the API documentation, either, so I don't think it's accessible to us.
@@bi-ome I saw in one of your videos that you posted that the value for 'completed by user display name' appeared, but it was filled with null everywhere, and I was thinking that there might still be a possibility. This was the video th-cam.com/video/RbcrJbwoIOw/w-d-xo.html
@@DariaOrlea Oh you know what, you're right - this is another one of those that doesn't show up at all in the UI. I was doing a search on outputs and didn't see it, I think because the plan I was looking at didn't have any completed tasks? I was able to get the completed-by user ID with the expression items('Apply_to_each')?['completedBy']?['user']?['id'] - you can feed that to the get user profile to get their name/email. Their display name seems to always be null.
@@bi-ome Could you send a picture of the changes you've made, since i'm also interested in the 'completed by' part for a report, so i can keep track? I'm still new to Power Automate and didn’t quite understand what to modify
@@lucavlad5669 I haven't made a flow modification for it, I will put it on my list to do. I just fiddled with a compose action until I got the ID reference right. It'd be handled similar to the assignees, but without a loop since there's only one value.
Hi Christine,
I have followed all your steps but getting a time out issue when I test it. I also received an email saying my flow is running too many actions and that it has exceeded its Power Platform Requests limit in the past 24 hours. How can I avoid this?
Hello! If you have more than 500 or so tasks, you will want to turn on pagination in the "list tasks" settings in the ellipsis menu and set the degrees of parallelism on the loops to 1. This will get all the tasks and avoid throttling errors. But, if you have a TON of groups, you might consider only getting the specific plans you need instead - I did a follow-up video on that here: th-cam.com/video/1UfxETalb9E/w-d-xo.html
@@bi-ome Thanks so much for your quick reply! I'll take a look at that video. I have about 20 channels, each with 1 planner list which can have anywhere between 5-200 tasks in it.
I have tried the Workflow. It worked when I created it for a specific plan but when amended for all plans I get the following error. Are you able to advise how I can resolve please?: Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'For_each_Bucket' at line '1 and column '3667' cannot reference action 'List_buckets'. The action 'List_buckets' is nested in a foreach scope of multiple levels. Referencing repetition actions from outside the scope is supported only when there are no multiple levels of nesting.'.'.
It sounds like perhaps things didn't get moved to the right level, or perhaps there's an extra loop in there?
You rock! Your videos are so helpful!
Nice video !
I've a question. Is there a way to generate the .json file only extracting information from an Specific Bucket?
Let's say I have an Operations Planner and I have these buckets: Operations, Maintenance, Safety, Manufacturing, and I just need to get a .json with Operations tasks?
I'm a newby on Power Automate :(
Thanks in advice, new subscriber !
Yeah, normally you'd just put a filter on the first query that gets the tasks, but it looks like "list tasks" doesn't have a box to add a filter like other connectors do... so what you could do instead is use "filter array" and filter on the bucket ID of the bucket you want after listing all tasks, then use that filtered array for your for-each loop getting the details. I wouldn't do this on the "get all my plans from all my groups" flow, I'd make flow for just the one plan here. Alternately, it's really easy to filter on the Power BI side in Power Query (like literally 2 seconds) once you have everything - so it depends on if you're filtering because you have so many tasks that the flow takes forever, or if you just don't need to see them all in the report. :)
@@bi-ome Finally I decided to filter directly from Power Query, that was just an idea 💡
I've tried creating different Flows for each bucket but, Power Query was the best option to.
Thank you very much!!!
Hi Christine, this is easier than using microsoft graph API to get data, especially because no premium tasks are required, but, do you know how to get the comments history from a task?
The comments aren’t in either these actions or the API to my knowledge :(
Hi Christine, after using this flow for a while I get the following error:
Your flow has exceeded its Power Platform Requests.
Have you experienced this ?
It sounds like you're getting throttled - you can try setting the parallelism on the loops to 1 to slow things down. It's based on how many calls you do in a period of time. Premium Power Automate licenses also have a higher allowance and I think faster speed.
@@bi-ome I'm also running into an issue of a similar flow taking somewhere around 3 to 5 hours to complete. I know that's very dependent on how many groups, plans and tasks I have. This is for about 259 plans and close to 4000 tasks. I just wish it can go faster.
@@carlomendoza7246 If we didn't have to use a separate action to get all of the details for the tasks, this would be much faster! The problem is that the "list tasks" only has half of it, and apply to each loops are always slow. I would really recommend people who are super heavy Planner users look at the Planner Plan 1 / Planner Premium, because it's $10/month and only the project manager needs a license. That product stores the data in accessible tables, and you can pull it with the Dataverse connector vs doing all this hijinks (a video on this is next on my list, promise...). It also has more features you probably want anyways, like a functional Gantt chart. 😅
Hi. How could I bring the attached document for the assignment?
Good question, that's in the "References Resource Link" dynamic content card - it's a hyperlink to the attachment. Since there can be multiple, I'd make another array variable called AttachmentLinks and append them in a loop to the variable after "get task details" and then stick that variable in a property on the final data array.
Not sure what you need to do with the attachment exactly, so hard to say after that but that gets things started. 🙂
@@bi-ome Ty!!!! It worked wonders for me. Greetings from Peru.
@@bi-ome Hello, me again. Sorry for the inconvenience. I have another question, is there a way to get the notes and comments for each task? :(
@@jurgenfelipemorenomilla9170 No, the comments aren't available in the API to get. You can get the description, but not comments--