Very Helpful and Love the pace you use as well (yes, we can pause, but some speak fast and don't have to rewind and rewind to understand what was said).
Hi Ben, Absolutely brilliant! I've rarely comment on any programming/technical videos, but all that hard work you did has massively helped me today. I even had a ticket with Microsoft about this exact issue and they only gave me vague details or reasons why it wouldn't work. I ran into some issues but mainly due to how I had things setup. Liked and subscribed as this information has been invaluable. Keep up the great work! ⭐
Amazing work. Thank you very much for this. I had a separate flow for each of my plans and half were failing for different reasons. I am doing my first run and everything seems to be working as expected. (Fingers Crossed)
Firstly, very very useful and awesome solution to consolidate all planner plans and tasks into 1 database and the ability to visualise in 1 report! However, I have tweaked the flow to run in sequence, 1 action after another. Managed to minimize the run-time to 2 hrs and 38 min, generating 1000++ tasks and checklist items. The parallel branches did not work as the flow kept running for 13 hours+ without any end in sight.
Hi, thank you so much for the feedback; it's great to understand how people are using and modifying this to meet their own needs. Any chance you could export the flow to a zip and email it to me please? i'd like to try this (assuming I can import it).
@@BenHoward_PowerBI is your email address the same as the one shown when importing the .zip file onto power automate? I couldn't find your email address anywhere else.
Hello Everyone. I have been using this Flow for about 10 days and it is amazing. I have roughly 30 Planners that I extract and the Run time was over 6 hours. One of the areas I was able to improve is the deletion of rows. If you create a script in Excel, you can delete the rows in seconds. this literally cut hours from the flows run time. Here is a sample: function main(workbook: ExcelScript.Workbook) { //Delete all rows in Plans table. let delete_tbl = workbook.getTable("Plan_tbl"); // Delete all row(s) at index 0 from table plan_tbl let numberOfRows = delete_tbl.getRowCount(); delete_tbl.deleteRowsAt(0, numberOfRows); You then include the Excel Connector to run a script.
Hi @James Bucahana - thanks very much for this. Just to say I have test this and it works well, unless there are zero rows in the table, this can be taken care of with an if statement to check if the numberOfRows > 0 . So the whole code is now... function main(workbook: ExcelScript.Workbook) { //Delete all rows in Tasks table. let delete_tbl = workbook.getTable("Task_tbl"); // Delete all row(s) at index 0 from table Task_tbl let numberOfRows = delete_tbl.getRowCount(); if (numberOfRows > 0) delete_tbl.deleteRowsAt(0, numberOfRows); } For reference, this reduced my runtime from 18 to 7 minutes. Thanks for your input, together the community can make many things better!
@@BenHoward_PowerBI Thanks for the feedback Ben. I will incorporate the If statement in my Flow. I hadn't run into that scenario so I didn't account for it.
Excellent video. But im puzzled why do you need a power automate since you can feed the excel file from sharepoint directly into power bi services. Am i missing something here?
Hi Ben. I have an Issue where the connections are not coming up. The import is successful but connecting it to my environment doesn't work because. the connections available on the environment don't show up in my list.
@ 9:24 in the video, you can see on the menu on the left hand side, click on Data and then Connections, and in the top click on New Connection. Set you your own connections to the services required.
It's difficult to to do this in Power Automate, but easy in Power BI. Add a Report Level filter to the Power BI report, and select the reports you need.
Hi Ben, at 8:20 add "Add Planner tasks to an Excel Online Business spreadsheet" as new. I'm trying to do this too, but I get an error "MultipleErrorsOccurred". Do you know why? The rest works!
Hey Ben, thanks for putting up this flow. Incredibly helpful as I need planner info to populate into Power BI dashboards. I'm receiving the error below - am I missing something? Flow save failed with code 'MultipleErrorsOccurred' and message 'The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'NotFound'. This may indicate invalid input parameters. Error response: { "status": 404, "message": "Script not found. It may have been unshared or deleted. clientRequestId: dfdab3a1-294f-4fef-a862-cc8cf675e122", "error":
Hi, I now use Excel Scripts to remove the files - it seems like you are getting an error regarding them - please review - ruclips.net/video/82aYZBKiwoA/видео.html
I have seen both videos about the load of data in excel and them Powerbi, but I have a problem that I do not no how to do it. I have many teams, and many planners and it takes forever to update the excel. Is it possible to tell which team I want to take a look to?
I am attempting to import the package into Power Automate and, after changing the import setup to "Create as new", I am not able to see or assign connection for each resource type. Do you have thoughts on the cause?
Hi, you need to set up connections for each resource type, you can do this from the connections tab in Power Automate, click on New Connection, and then it's best to use the search box in the top right (eg Excel) and then create your connection. Hitting refresh then back in the Power Automate import wizard on the connection will show you the new connection for the relevant connection type.
I am unfortunately stuck at the import step, where I do not have an option of a Office 365 Groups Connection. At what point in the flow is this required? Can I just manually create the power automate steps as shown in your tutorial?
Hello Ben. I am confused with the report in power bi, the template was made by you? I would like to learn how to create a similar template, do you have any video or recommendation? Thanks a lot
Hi Dylan, yes, the template was made by me - why are you confused with it or the report? To learn how to create a template please review - docs.microsoft.com/en-us/power-bi/create-reports/desktop-templates
Hi Ben! First of all, thank you very much for the video and all the fields shared! I was getting a few problems with my word and you help me gracefully! However I would like to see two things about the Labels: 1- how can I change the labels from the colors name to the labels I'm working in Teams? 2- is it possible to do a chart by label? Thank you again for all the help.
Hi Carla, alas a limitation of the connector is that it only recognises the pre-determined label names, ie the colours. It certainly is possible to do a chart by label, you should be able to easily amend an existing chart to display the label instead of one of the other categories. A simple way to do this would be to duplicate the page in Power BI, and then amend one of the charts on the new page.
Looks realy nice but it seems this is no longer up to date with the current flow version. Now it uses a script to remove the excel lines but I can't seem to get that working properly. Do you have a seperate video talking about that?
Hi Ben. It's a giant and very helpful job. I have an issue. Hope you can advise. The issue is duplication in Task Sheet in the Task_id column. It causes the PowerBI error as well. I can not understand why the duplication appears. And how to remove it or fix potential problem in the power automate script.
Okay; this is typical troubleshooting and this issue can occur if you have two flows running at the same time. So, make sure this flow is not running. Then to test the script that deletes the rows in the task table, open Excel and run the correct script. This should empty the tasks table. Then, just add some stuff into the table, just a couple of task names into a couple of rows, nothing major. Keep this excel file open so you can see the task table. Run the flow. You should see the table empty, and then fill. You shouldn't get duplicate tasks, and typically it is either because the correct script is not run, or people are impatiant and start another instance of the script before the 1st instance has finished.
This would be helpful. I'm still new to Power BI and was able to follow along well enough to get this working for me (huge success! thank you!) - but the number of Teams I have is a problem. I've only created problems for myself when I try to adjust the flow to just look at one Team.
Hi Ben, thanks for this. However, on your github page, the attached file was updated into removing rows from the tables? Should I change the action instead? Thanks very much!
Hi Roland, This solution has evolved over time, alas it is not possible to modify existing youtube. So, please watch ruclips.net/video/oWyDJmDDFfU/видео.html and then ruclips.net/video/82aYZBKiwoA/видео.html - any questions after that, please just post them and I will do my best to get back to you.
Hi Ben, Thanks a lor for this video and you useful insights! I was trying to draw the ppwer automate flow chat by myself but I blocked since in the "add row into a table" task I'm not able to find all the "value" dynamic plannwr contens to fill the several columns of the table. Could you please tell me why it happens? Thanks so much Marco
Hi Marko, if you reach out to me via email (you'll find my email address in the Power BI template) or online etc, then we have a look via a teams call.
Hi Ben, having a problem running the flow (which took 3 hours - 132 plans), Seems to fail in each table branch (x4) at the very last action. In the final action of 'apply to each/List plans for a group' I'm getting the error message - 'ActionFailed. An action failed. No dependent actions succeeded'. It appears to have only failed on the very last of the actions, as scrolling through them only the last seems to have failed (141st out of 141). 140 out of 141 has a green tick... Any ideas?
Hi Stuart, alas no, typically for me when i have been developing flows like this either everything works or everything fails in terms of adding/removing data from Excel sheets.
Thank you for posting this. I am working with the original flow. I am running into an error because I am associated with 13 teams and most have a planner associated with the group. I changed 'Gourp Id' on the 'List plans for a group' & 'List plans for a group' to a specific group. But it is still running through all of my teams, which is taking over an hour, before it fails. Is there a change I can make to specify only one team?
Hi bob creek, there are 6 branches to the flow, 5 of them (all except the users branch) will list plans for a team, so you'll need to troubleshoot all 5 branches. However, if you are only interested in a single team, then I have just uploaded a flow to the repo that does just this - github.com/ben-howard/Planner-PowerBI-with-PowerAutomate/blob/main/SingleTeam-AddPlannertaskstoanExcelOnlineBusinessspreadsheet.zip . The excel and power bi file are both the same. you'll also find the associated flow map.pdf in the same repository.
Hi Ben, I’m making progress getting a single team task to load and the flow runs without issue. The only problem I’m having is the data is only being shown in the task and Team members table. Any ideas? I’ve crossed checked everything without finding any errors
Hi Allan, if you ping me on my email we can do a teams call and you can check your config. It should all work nicely, it is likely something is mis-configured.
@@BenHoward_PowerBI, thanks but I’ve sussed it. I had to change in the “list plans for a group” from the name of the Teams site to a custom value “Team ID”. When I listed the team name no raw outputs were appearing. Working a dream now I think, the proof will be if it refreshes tomorrow morning.
Hello Ben and thanks for the amazing tutorial! 👍 Would it be possible to restrain the Teams pull to only some selected Teams instead of all? I cant quite figure out the Flow there. Thanks!
Hi, for each branch of the flow, in the List Plans for a Group, you could replace the Team ID with the specific group name you are interested in. I am pretty sure you could find a way to provide a list of team IDs, but they might need to be stored in a SharePoint List or similar and then you could read and loop through this.
There seems to be an issue when configuring setup for the imported flow (8:44); I don't have the Names in the 'Import setup' pop-up, and clicking on 'Create New' just takes me to a different page where all the connections are...
Did you read the whole of the description and watch the related videos? Learn how to configure a Microsoft Flow using Power Automate to automatically take tasks out of Microsoft Planner, import them into an Excel spreadsheet, and then automatically refresh that data into a Power BI report. The soln has been updated to use Office Scripts, so be sure to watch ruclips.net/video/oWyDJmDDFfU/видео.html and ruclips.net/video/82aYZBKiwoA/видео.html aswell. The files to enable this are available in GitHub @ github.com/ben-howard/Planner....
This is great. Thank you so much Ben! One question you might know the answer to: I have renamed the categories (red, purple etc) to actual work stream names. But I can find the value under tasks to get those names to excel. Could you have a look?
Thank you so much for this! Just a quick question please, it doesnt seem to be pulling up the labels in the powerbi, is this correct? There are labels that have been created in the planner but are not seeming to sync through. Can you help please? :)
That's correct, it will only bring in the colour of the label, not any customizations. If you need these, then the best way to do it is via Power Query, ie replace Red with "urgent" or whatever your customization is.
@@juniorjay911 - Hi, you replace values in Power Query - this video shows how to use Power Query to change or amend date values ruclips.net/video/_AagDlq3BnA/видео.html - or support.microsoft.com/en-au/office/replace-values-power-query-28256517-f1e9-4dc3-832f-45786e9cf721 - you open power query by clicking on the Transform button in the Power BI ribbon.
Thanks a lot for this video! Is it possible to use this process or similar to make a company level dashboard of everyone's tasks - even ones where I am not a member of the Group or Plan?
Hi, you could run the flow with a service account that has access to everything; though there are licencing implications in terms of how flow is licenced.
Hi Ben, I know it has been a while since you published this group of videos. One question -can I add the Priority by just adding a coln to the Planner Tasks excel? If so, should this be done in the Task_tbl table? Thanks
@@BenHoward_PowerBI Thanks for getting back to me so quickly, really appreciated. I bit of a shame, as prioritisation is rather important to planning and execution.
Very helpful, thank you! Do you have any tricks to skip failures if there isn't a plan in any group? I'm getting an error: type 'Null'. The result must be a valid array.
@@BenHoward_PowerBI I'm facing 403 Forbidden error, list plans for a group this will sometimes fail the condition for which it fail are if you are not a member of the group that you own "statusCode":403,"headers "message":You do not have the required permissions to access this item. expression '@outputs('List_plans_for_a_group_3')?['body/value']' is of type 'Null'. The result must be a valid array. I found this, around minute 9-10 she is doing a troubleshooting when this happen: ruclips.net/video/mqGFbIGpCcw/видео.html
Hello Ben, Goodday, Please i was able to follow all the steps until configuring the connection for each resource type. I am not able to select an account so i have been stuck on that step. I would really appreciate your help
Hi, have a look at the video @ 8:37. You can see in the side pane on the right hand side a button called "+ Create new". At this point you are going to create a new Microsoft Teams Connection. From memory this will open a new window, and from there you can click "+ New Connection" at the top of the screen, and then I normally search for the connection, so just type in Teams from the search box (top right). Select the Microsoft Teams connector to create the connection. The interface can also be found in the menu on the left hand side under Data | Connections.
Hello Ben, Good Day, I tried creating a new connection but it only comes up for one or two resource types. For the other resource types, even after i refresh it doesn't seem to be working. Any idea what i can do differently?
Hello Ben, I was able to figure out what the issue was. I was using just a Microsoft Teams connection for all resource types, meanwhile some were Excel Online (Business) and Planner connections.
Thank you for this example. I am having 1 issue. All of the task are not being removed so I'm getting an error. I have duplicates. Any ideas on how I need to modify the flow to ensure that all of the existing data gets removed. So that I get a complete data refresh?
Hey, thank you for the video although I tried making your workflow but for me the loop never ends, and I get timeout error and thus no data is imported into excel. can you please guide me through it? Thanks in advance.
Hi Ben, fantastic video, many thanks for this. Unless I've missed something, is there a way of combining multiple plans from multiple Planners/groups using the same solution please?
Hi Paul, you missed something, this is exactly what this does, it combines all plans from planner into a single report. Unless I misunderstood the question.
Yes, this is relatively easy to get the data into the Users Table in Excel (just add in another column) and then map the team name to the new column in Excel. However, Power BI then does some cleansing on the user table as a single user may belong to many teams (basically I remove duplicates), and so I'll have to think about this and produce a new Power BI template, or you could :)
Hi Ben, very nice job! The only problem is that is not more possible to import into PowerAutomate. There is an error in the first tasks of the import operations. They said "'MultipleErrorsOccurred' and message 'The dynamic operation request to the' GetTable 'operation of the' excelonlinebusiness' API failed with status code 'NotFound'."
Hi, you would need to modify the flow so that you enter the plan names or ids. Alternatively, you could place a page filter in Power BI to limit the plans you view.
The Power BI report can be viewed at app.powerbi.com/view?r=eyJrIjoiODliMzA1N2EtNzczZi00ZmViLTgyNzItYTEwMWViZDMyNTA4IiwidCI6ImI0MWI2YWQ5LTViZDAtNGNkYS05NWI3LWI0NDhkMGM2NTZiYyJ9 Note that one user has had real performance improvements (about 4x the speed) by modifying the flow to write to Microsoft Lists instead of Excel. Obviously both the flow and associated Power BI template need to be amended to use the new data sources, but if you are impacted by a slow flow then this might be a good solution for you.
Hi Ben - I am receiving an error that the content isn't available. Is there another way to access it? Thanks as always for all the expertise you provide!
HI Again, so my flow takes 4 hours to run, so I'd like to investigate the Microsoft List option. Are you suggesting that I create a list for each of the tables that exist in the PlannerTasks spreadsheet, and then somehow get PowerBI to load each Microsoft List?
@@peterlabermeier215 - You will need to create a list for each table (this is quite easy as you can create a list based on an Excel table so thate will create all of the columns for you). Then you will need to modify the flow to use the list, rather than the Excel sheet, and then you'll need to modify the Power BI file to read from the list rather than the Excel table. I would start with doing this for a the tasks portion of the soln initially, as this is probably what is taking the majority of the time in the flow.
Hi Ben, The flow really works well. Do you know if there's a movie to modify the flow using microsoft lists? I'm a real noob but it would be a big improvement for me because the flow is taking around allmost 3 hours to complete. I would like to schedule it somewhere around every 4 hours starting before I start work. Thank you for your help. Greetings from the Netherlands :)
Hi Ben - anyone else's flow taking an age to run? Mine is stuck on the "deleting" elements for each branch and has been now for nearly 10 mins. Don't have hardly any data in the Plans as I am trying to test it first . I am using existing groups, teams, document libraries etc. but everything else should be working correctly shouldn't it?
Sometimes this is just flow running slowly. Did it finish correctly? For ref, I have just initiated this flow and it's taken 10 mins to create 30 tasks.
Hi Ben, thank you so much for sharing this, the video and files are incredibly helpful! - I'm stuck in the part where I have to open the .pbit file; I entered the URL of the excel file but right after that I get message to "Access Web content". I tried using my credentials, but I keep getting a "We couldn't authenticate with the credentials provided-Please try again" message. I also tried using my boss credentials since she is the Global admin but keep getting same error. Do you know what could be causing this problem? Appreciate any advice I can get! Thanks again.
Hi, I assume your URL is in the following format? tenant.sharepoint.com/sites/APOGroup/Shared%20Documents/PlannerTasks.xlsx. If you can't fix it reach out to me and we can schedule a teams call to try and fix.
@@BenHoward_PowerBI Hi Ben, I was able to fix the error. Now, I'm running into another issue: all my plans are ongoing plans with ongoing tasks, and I just need to track and visualize In Progress, late and Not started tasks, I don't need the completed tasks, and I think that's why the flow is taking so much time because is listing ALL tasks. Is there a way to run the flow without the completed tasks? Appreciate any advice you can give me. Thank you!
Hi Ben! This video is amazing. Thank you! I have a silly question. At 8:48 when you're configuring the package content, I'm able to configure all except the Office 365 Groups Connection. My account doesn't pop up for that specific one. It appears that everything is connected, but I'm not sure why that one is off. Have you run into that before?
Within your powerautomate environment, click on Data on the left hand side, followed by connections, and then select the + New Connection button at the top of the screen.
Hi Ben, I am looking to only include one plan from planner. How do i only bring one through the powerautomate rather than all that i am linked to in planner?
Check out the github repository in the clip description - in there you will find a flow that will allow you to link to a single team. You many have to filter further in Power BI, or you may be able to hard code the planner Id in the power automate, but using the flow I published for a single team will be the quickest way to get started with this.
@@BenHoward_PowerBI May I ask a quick question …. How do I get the assigned to user ID to come through? I have added the ‘value assignments Assigned to user Id’ and the flow runs but doesn’t bring through the data. Have you experienced this issue?
@@bethnorbury8644 - the assignees already come through, you don't need to modify anything. Check out the User slicer in the Planner Tasks tab. Note that the tasks table has some additional columns in there which aren't directly populated, because each task can have > 1 assignee.
Hi ben, thankyou for your video. But after running the flow I found out that the checklists table is populated with many duplicate checklists, in my case it was 610 distinct checklist id's, and my table contain 4000 in it
Hi Ben, I have run into an issue at minute 8:42 where my related sources won’t show a connection for my to select. I can only select a connection for three of the related sources but there is no connection in actions for “Microsoft Teams Connection, Office 365 Groups Connection”. Would you happen to know why this is happening? Thanks!
Hi Ben, Thank you for this. It was very helpful. I wanted to ask if there is a way the notes in Planner could also be transferred over to Power BI? Please let me know. Thank you!
Hi Ben, Thank you for getting back to my comments. I really appreciate them as they’ve helped me a lot. I have one more question and that it, the flow keeps pulling information from two different groups in Teams. In my flow I have specifically assigned it to come from ONE team but it pulls information from two teams. Making the buckets plans etc all for both the teams. Would you happen to know why this is happening?
Hi Ben. Can you pls tell why I’m unable to see any connection to modify each resource type? Also creating connection is also not helping. Any tips pls?
It'll be because you don't have any connections set up. Open a new Power Automate tab in your browser, click in data > Connections and then New Connection (at the top)
@@BenHoward_PowerBI thank you very much. That worked. I tried importing thereafter, however I got an on the Add Planner tasks to Exel... 'Multple Error Occured'. Is there a fix for this? The other related resources seem to be fine.
Hi Ben. Great video as usual. There is one thing this leaves me pondering. Would it be possible to set up the Excel file to track how long tasks are sat in a bucket. Then report this on Power Bi. Just thinking about it as a process engineer and wanting to know average times to complete tasks from various buckets?
@@BenHoward_PowerBI yes it is most annoying. I was thinking to time stamp the task to a bucket using excel, granted this would only give the time that the flow ran at. But at least it would be something of an idea.
@@roylewis5081 - would require some serious mod to the flow, as currently each row in Excel is removed during the flow execution.Somewhere you need to keep the history of each task. It might be possible just to append the tasks into the Excel file and do some work in Power Query to remove duplicates (along with adding in the timestamp). You'd always have the issue of deleted tasks though, you would need to remove those manually from the Excel file.
Hi Ben! I cannot seem to get the checklist is checked data to import to the Power Bi correctly. The excel file shows the correct true/false for which items are checked and not checked, but on import the entire COMPLETED dataset in Power Bi just reads "no". I've tried changing the data type to True/False for the completed data set but I get a "not allowed" message. I am not sure how to fix it, but it would be very nice to be able to see if any of these items are completed instead of having everything be grouped under "No" regardless of true value. Any help would be appreciated.
Hi Ben, Thank you for this awesome video! I just have also one more question. Is it possible to create an visible green or red Circle in the power BI Dashboard, when the status is completed or for example after the due date? I have problems with this because power Bi just accept Values for creating such a circle. But the status is just a text. Maybe you can help me here. Thank you already !
Hi Ben. Nicely done : -) Works out great. Thank you very much. Just a few observations. Any chance that you could the priority field from planner in the next version? I tried to add it myself but it did not pull any data into the Excel table. In addition I realized that SharePoint use minus signs in their Id's which Excel translates into the start of a formula which results the "NAME" error. The error causes that Power BI to fail when refreshing data. One workaround is to make a "Search and replace" in the entire workbook where you search for "=" (equalsign) and replace with a single quote ('). I then tride to code a macro in vba that would undertake this task it does not seem like Power Automate can handle macro-enablede workbooks (xlm-files). Please let me know if you find a sollution for this, Have a great weekend
Hi, I'm glad you like the soln. Re the priority field, alas it is not exposed via the planner "list tasks" action in Power Automate. You can use the feedback link from the gear wheel icon in Planner to feed back to Microsoft and ask for this (Microsoft are deprecating their use of user voice and I believe this is the best was to feedback at the moment). Re the minus signs in "SharePoint", I am not sure what you mean. SharePoint is simply somewhere to store the Excel file; please elaborate on your issue here with specific examples...
@@BenHoward_PowerBI Thanks for your response. I have listed a few of my tasks here. The task ID "-6VlqpkSM0eczlpof6ZQo5cAGbu8" has a minus sign in the start of the ID. Excel will assume that the Task ID is meant to be a formula. Therefore Excel will show the #NAME? error. Task ID Task Name Bucket Name Progress Priority HP9REq3HP06S9cl4PoZJB5cANYsf Goto Company hjemmeside Goto Administrationsopgaver Not started Medium -6VlqpkSM0eczlpof6ZQo5cAGbu8 Flytning af Webinar - Leads Migrering af StudyNow Not started Medium I will try to give feedback to MS regarding the priority field. Cheers Bo
@@bofrederiksen5022 I had the same issue. I fixed it by editing the Flow and adding an apostrophe before the field. It auto-concatenated it and writes it to Excel with the leading apostrophe on all TaskIDs which works well.
Hi Ben, the flow works really well! thank you so much. I was wondering if there is a way to fill also the field "Assigned By User Name" in the "Task_tbl". I have a need of creating a total overview including User Names but so far only managed UserID´s. Would really appreciate any hint.
Hi Ben, thanks for this very creative flow. Actually i am part of many Teams and there are lots of plans there. Now the flow is extracting everything from each planner. is there a way to restrict the load from any specific sharepoint / Teams to reduce the loading time ?
Great video! So far the flow works, but it takes awhile. Is there a solution in which my old tasks can be preserved, and then only the information for new tasks are added from the planner to this growing database, without having to wipe the data in the excel tables? In addition, I’m running to an issue with the checklist option in which the checklist table is not populating.
@@loganedmond2932 - Hi Logan, if you don't wipe the Excel file how do you work out i) which tasks have been updated ii) which tasks have been deleted iii) which plans have been deleted. There is no easy method to solve this, and therefore the easiest soln is to remove all the existing tasks and re-import them. It is true that this is not an "elegant" soln, but it is a pragmatic and simple soln given that, due to the nature of the data, it is not really time critical (viz. a viz. transactional data) and will often only be updated at night, and therefore whether the refresh takes 2 seconds or 2 hours it is unnoticed by the end user. Re Checklists, they should work, please check your scripts.
Thanks a lot for this update! It is very useful but could you please add a Gantt chart with conditional formatting to this excellent template? Secondly, is it possible to update the PowerBI report only when a change is made in Planner? Thanks again!
Hi, you cannot initiate a flow unless you specify which plan you create or update the task in, so I'm afraid that is a non-starter. As for conditional formatting of a gantt chart, all the gantt charts I've worked with in Power BI don't support any conditional formatting, so again, alas it's a non-starter. If you are worried about the cost of running the flows, I suggest you just set the update to completely be manual, and then run this when you need to. Power BI can be set with a pro licence to refresh up to 8 times a day automatically with no additional cost, or you could manually initiate this refresh too.
Hi, Ben Thank you so much for the video. I have one question. When you get to the connection for each resource type portion I don't have any of the same options in the list that you are showing in the video. Is there a reason for that or am I missing a step by mistake?
Hello Ben, Thank you very much for sharing your knowledge and congratulations for your work! I hope you could assist: Could you confirm that the limitation for "Delete each row" is 256? In the Planner task Tables with more rows than 256 we will need to delete it in the excell doc, have you any magic for this cases? Thank you for your time you have a big fan here!!
Hi, by default it should return all rows, however there seems to be a bug in the connector. To fix this edit the flow and for each "List rows present in the nnn_tbl" Excel connector, set the top count value to be 1000000 .
@@BenHoward_PowerBI Hi Ben, thank you for your answer, you are right i have set pagination limit to be 1000000 and it worked, but my table still having errors with duplicate values in the id buckets, tasks.. I didnt have this problem deleting rows in excell of PlannerTasks.xlsx so it seems "Delete each" dont eliminate all the rows. By the moment I have included a "run a script " deleting the rows in your flow, Do you think this could be insecure? Thank you for you time
@@AndresGomez-gu2zd - Potentially it is worth manually deleting the rows in the task_tbl to remove all rows (leave the header row and a single blank row). This has worked for me in the past.
Thanks for this. It seems that it will solve one thing I've been trying to do. Tried to import the flow, but the User Groups connector does not show to me, and I can't even create it, any idea wh?
Thanks Ben, lovely work here. Is it possible to get the Assigned to filed populated as well currently its blank and only give the Id as dynamic content in the flow to be attached to in the Assign to column.
Hi Ben - great video and thanks for sharing to help others like myself. I have implemented the flow and it works, creating the excel sheets. However, when loading into PowerBi I get a message about duplicate entries in the ID columns e.g. Plan ID or Bucket ID. Opening the excel file shows me 47 identical entries in Plan ID. Looking at the flow for plans, I can see that 'List teams' produces a list of the 47 teams in my org. The 'Apply to each 5' and subsequent steps then writes the Plan ID to the excel file 47 times, hence the duplicated items. This conflicts with the 1:* relationship rules in PowerBi and prevents it from loading the task data. I can't see any differences from what you have shown in your video and wonder if you have any suggestions as to what I can try. Thanks again.
Hi Mel, I also had this issue today, in my case it duplicated each task 22 times. There is a definite issue with flow at the moment, but I haven't had time to investigate. If it decides to fix itself, or I find something out from MS support, I'll post it here!
@@BenHoward_PowerBI Hi Ben, I had the same duplicate problem yesterday and today. Have you come up with a solution yet other than recreating the task table? Thank you and I really appreciate your work. Have a great day.
@@BenHoward_PowerBI Hi Ben, can you let me know how to recreate task table? Thank you so much. I am trying to fix the duplicate issues after each Refresh.
@@HanhNguyen-mf2ji - I would open the existing excel file, and remove the table sheet. Then re-download the excel file from github, open it, and copy the table sheet from the github file into the the file you just removed the sheet from.
Hi Ben !!! I have a problem , I have the same error when I import the data that you explain , but in “next steps …” I don’t have the link of “Save as a new flow” so I can not check the path of the tables as you do ! Do u have a solution ? Thanks so much
Hi!!! Yes ! I add that query parameter to the URL before import the file . And after I recibe the error but it doesn’t appear the link to check the path .
Hi Ben, thanks for the video. I have one challenge with my task boards in Planner. I have one team channel for our department and then private sub-channels for different teams where i add activities for the specific team. For the sub-channels as these are set up as private groups I was only able to create the task boards via Planner on the web and then adding a tab to each sub-channel, with a link to the appropriate Planner website URL. When I run the Power Automate Flow, those plans do not pull through onto the excel template. Do you know if there is a work around for this so they would be included?
Hi, the user who runs the flow must have permissions to view the team and then the planner plan must be associated with the team, which it will be if it can be added as a tab to the channel.
@@BenHoward_PowerBI Thanks. I think that is my issue. As I added the plans directly in the Planner app there are not technnally associated with the sub-team channel team groups. It is only linked there with a tab and weblink.
@@BenHoward_PowerBI Thanks again. Unfortunately, I have found it is one restriction with MS teams when using private sub-channels within a team group. It doesn't allow you to add multiple plans through the Planner app to different sub-channels so you cant directly link them to the team. You can only create it in MS Planner web and add the link via the website app as a tab within the sub-channel. The members you have to add manually in planner on the web so there is no actual direct link to the actual members in the sub-group. Hopefully MS come up with a solution in the future. Only work around would be to remove the sub-channels and create them as individual team groups in their own right.
Thanks for the great job Ben. I seems to have issue while exporting to excel with the path link. Kindly shed more light on how to work around the following error: Column 'Task ID' in Table 'Task_tbl' contains a duplicate value 'ULIKnWxby0mugRlEsggGAmUANNoq' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
Great work, thanks for sharing this Ben! Just wondering what is the reason you run the steps for Tasks, Buckets, Assignments etc in parallel rather than in series
@@BenHoward_PowerBI I have some real world testing to share, the flow with parallel branches is taking on average 1.5 hours to run with about 6,000 tasks. I have also split each "branch" into separate flows all triggered to run at the same time each night and the longest duration is 0.5 hours (assignments branch)
@@Bren4000 - that's great feedback, thanks for posting. The length of time the flow takes could be important if you are working in an organisation that spans multiple timezones and geographies, in which case you might need to squeeze the flow into a smaller window.
@@BenHoward_PowerBI no worries Ben, happy I can contribute. my main reason for optimising the speed is because I want to be able to trigger the flow from a button in power bi so users can refresh any changes they make to tasks in teams. it looks like excel is the bottleneck now because the separate flow branches run faster on their own and take longer when triggered together. I'll try splitting each table out into its own spreadsheet and report back.
@@Bren4000 - Cool. Be aware that with Pro you are limited to 8 refreshes per day or I think 48 with a premium account. Refreshing via a button counts as "automated".
This was really helpful! Thank you! Is there a way to do it only for a single planner instead of serching through all my teams? I have a lot of teams and plans and it takes a very long time to run the flow.
HI! Ive been trying to make this flow on my own but Im running into some trouble getting all the info. Is there a chance you could do a video? Thank you very much in advance!
Hi Ben - this is super helpful and skillfully done - thank you! I was also wondering if you've had any luck creating a flow that refreshes an Excel file based on all Task detail, including checklist items, etc.? Many folks in the Microsoft community are exploring this feasibility with limited success, since apparently "Get Task Details" can't be used in a loop ("For Each"). Thanks for your thoughts and expertise on this!
Hi Matthew - thanks for the comment and the nice words contained therein. You can use the Get Task Details in a loop, I've just modified my flow to do just that, and have pulled the checklist items into a separate table. I will update the files on github when I have updated the Power BI report. I'll post another reply here once it is done so you will notified.
@@BenHoward_PowerBI Hi Ben - I was just wondering if you happened to tackle this - it would be of tremendous value to many folks (including myself!) I am sure. Thanks so much!
@@OddPoliticalBedfellows - Hi Matthew, just published on GitHub with Checklist items. Please see page 3 at app.powerbi.com/view?r=eyJrIjoiOTU4NzVjNmQtZjA3Zi00YzVhLWEwOTMtNzRjMDMzZTgxOTIzIiwidCI6ImI0MWI2YWQ5LTViZDAtNGNkYS05NWI3LWI0NDhkMGM2NTZiYyJ9
a very good use of office 365 tools. Gerar job! I'd like to ask if it's possible to instead take the planner of a group, take the planner of a specific team.
Everything is working great.....except, the sharpoint site I am working with contains multiple planner boards. How do I get it to connect to the correct board?
Hi Ben and thank you so much for this tutorial. You really made it understandable for newbies like me. I just have 1 problems which also occurs on all flows i made myself and flows from other sources... The flow collects the data as supposed, but it duplicates alle tasks atleast 5 times, and i really cant figure out why.... Do you have any ideas of why it duplicates alle tasks?
Hi, I am glad you liked the video and it helped you. You say you have this issue with other flows as well as mine, so it would seem to be a problem with with something other than the flow. I wonder if your plans are associated with more than one team? If you cannot find the issue in flow, to solve the issue I would remove any duplicates using Power BI, but beware; each task can exist multiple times in Power BI because it will exist for each assignment, so if two people are assigned to a single task, the task will appear twice. To remove the duplicates you need to remove tasks where the task Id and assignment ID are the same. If you are not familiar with Power Query in Power BI then feel free to contact me again.
@@BenHoward_PowerBI thank you for your fast response and help. I dont think its because of seveal teams, because i tried something like this with a private test-plan, and it still returndet the 2 test tasks several tims... i really cant figure out why. To remove the duplicates in Power BI Query is also the only solution i can think of so far, but all the duplicates just slows down the flow a lot....
@@ABechT89 - this appears to be wierd! Is it just the tasks that are duplicating? If you watch the flow, does it fully remove ALL of the tasks from the task table in Excel. You should be able to open the Excel file whilst the flow is running to and see it empty (pretty quickly).
@@ABechT89 - I would ask someone else to have a quick review of the setup, sometimes we're too close to the issue to see it - I know this happens to me all of the time. In the meantime, if I can think of anything I'll let you know, likewise, if you manage to find your issue, please post here so that others can see the solution.
This is a huge help thanks and *almost* exactly what I needed. I would like to filter the “List teams” so I only get results from some of the many teams that I am a member of. Any ideas would be appreciated.
Updated today - please check and subscribe to github.com/ben-howard/Planner-PowerBI-with-PowerAutomate to be auto notified of future updates. The flow, Excel and Power BI template have all been updated. If you want to edit the flow then it's pretty easy. First you'll need to create a new column in the Plan table in Excel ( eg call it Team Name), and then edit the flow in the Plan branch to map the Team Name to the column. Run the flow, and then when it has been refreshed, edit the Power BI file to add in a slicer for the team name. If all that is too complex, you'll have to reimport everything.
@@BenHoward_PowerBI I do not find die solution for this problem yet - Where should i change the Flow? to only get for example one specific Team. Thank you in advance!
Hello, thank you so much for this video its helped me tremendously, however when I import my excel file into Power BI it doesn't create the singular table and the Task table doesn't have any of the computed columns I see on yours. Any ideas why this is happening?
@@KhanhNguyen-kg1xi - you need to use a parameter to hold the URL, and then use the parameter in Power Query when accessing the data source. When you export the file as a template, Power BI knows about the parameter and asks for it when someone opens the template. Have a look at the Power BI file that you created in the scenario covered in the video, and you'll see the parameter used and you'll be able to see it in Power Query too.
@@BenHoward_PowerBI do you have any video tutorial about this? please share with me :D I aslo have create my own PowerBi template but when user enter thier excelfileweburl, it requires authentication permission
Very nice and thank you for posting for us. I had some of the errors listed worked through them and was doing a manual sync to Excel. I was waiting for the sync to finish before trying the PowerBI part. I was literally watching it sync and it was almost done at 2 hours in when it just started running another instance on its own and started deleting rows while it was trying to finish. The flow is at the default of 2300 and this happened at 1940. Any idea why another instance would get kicked off?
Hi Steve, I'm glad you like the videos etc. I've no idea why the flow should start again, my runs consistenly @ 11:00pm every night! Please do post if you find the answer.
@@koshields303 Hi Kelley. From what I remember it only happened a few times and I never knew why but I very soon after converted it to work with Microsoft Lists instead of Excel and it's been working very well since.
Hi Ben, thank you for this video. I am importing the zip file onto Power Automate, I notice that the zip file I downloaded is called Planner-PowerBI-with-PowerAutomate-main.zip, and your file is called AddPlannertaskstoanExcelOnlineSpreadsheet..So my upload failing. response Something went wrong. Please try again later.. Please can you assist correct. thank you
Hi, it won't be because the .zip file has a different name. Did you see the subsequent videos and updates to the description, such as.... To import the zip file you need to add the query parameter "v3=false" to the end of the URL when you import the file. For example from my tenant the query is make.powerautomate.com/environments/Default-746993bd-b693-45c8-8ec1-f4755c4b728a/flows/import?v3=false from ruclips.net/video/82aYZBKiwoA/видео.html
Great video, thanks for making it. I’m giving it a shot myself and can’t seem to get the connectors working / assigned for the flow… (~8.33 in the video.) I’m still pretty new to it all, what connectors would I use if I wanted to build this manually? Or do you have a course on how to build this from scratch?
Hi Emile, it's worth ensuring you have already connected to the services and then the connections should appear, or just click on "Create New". The connection is basically your username for the M365 suite of apps, and of course this username has to have permissions to access these connections/services.
Hi, Im having the same issue. When I select create new it takes me to all my connections and all say connected including office 365. When I go back toi refresh it does nothing.
Hi Ben, thanks for this. During the import I can't select my account (no account is showing once I click) in "Office 365 Groups Connection" can you help me to figure out why? All the other fields I can add my account...
@@BenHoward_PowerBI 8:35. When selecting the connections to each resource type. All work but the last one "Office 365 Groups Connection", when clicking on the wrench nothing is shown. Not my email address nor an option to login...
@@BenHoward_PowerBI didn't work, but I solved. Sharing here in case it might help others. I created a new blank flow triggered by "Office 365 Groups - Add member to group". This brought me to a login which then appeared in the import step with your flow. So, solved for me. Thanks again for the brilliant work.
@@gogogolem Hi Ben and Angelo, Ben great video and exactly what I was trying to do. Small problem - I had same issue as Angelo, didnt get what was your (Aneglo) workaround, if possible could you please explain more in details.
Hi Ben, thanks for sharing the files and this thorough walkthrough. I've tried to apply it but keep getting the following error in Power BI: "OLE DB or ODBC error: [DataFormat.Error] Invalid cell value '#NAME?'.. An unexpected exception occurred.". Could it be related to unsupported characters used in tasks (Hebrew in my case)? Thanks!
@@BenHoward_PowerBI Hi Ben, i ran into the same issue, from what i observed and checked some not all the cells where #Name? was present the cells where the IDs of Buckets, Tasks were stored, and these Ids had an '=' at the beginning , which was resulting in excel thinking there is a formula in the cell and showed an error to correct as such, i dont know if this is how the Id originally is in Office 365 or was being added at the time of writing to the sheet by the flow. Just to proceed i deleted the buckets or tasks which had this issue and re-added some of them so they would have a new Id, and so for now i was able to get the visual in the report. So i dont have a file or anything to show you, but i am hoping there is a fix for this if you have any clue
@@dnesher - Well my Hebrew is non-existant so the data would be safe with me :) . The .pbit file will have a locale of English (United Kingdom), after you've got the error, can you change the Options to that the regional settings for the Current file are set to Hebrew (Isreal). This setting is used for numbers, dates and times for imported text. You'll then need to refresh the report so see if it works...
@@BenHoward_PowerBI That doesn't seem to solve the issue 😕 However, I did notice another weird behavior - when I open the excel file for editing in the desktop app, I see different data than online. On the desktop app it seems to be data from previous runs of the flow (before I modified it to focus on a specific plan), whereas on SharePoint I see the expected results. Does this sound familiar or related in some way? Is it a cache issue perhaps? I'm not sure how to tackle this.
Hello Ben, thank you very much for the explanation, so far it is the only thing that has worked for me to obtain a report in Power BI from Planner, I want to ask you if the time of execution of the flow has any time zone configured, since I see that this is it executes at 23:00 hours but checking the execution history it did not, my time zone is (GMT-6), there is some step that I need so that the flow is executed automatically at the time that it is scheduled. Greetings.
Hi, AFAIK the 23:00 should be your local timezone, but of course feel free to change and experiment. I am sure whatever time you set is only an approximation anyway, I think Microsoft "smooth" the timings so as not to overload their servers.
Great video Ben, Thanks! Unfortunately, I am unable to open up the Power BI file you have created as I'm not on the same version and unable to update. Do you have any instructions at all on how you set up the Template or is there another way I can access this file? Ideally, I am looking to be able to show tasks by priority, tasks by progress and tasks that are late on my Power BI report.
Hi, Microsoft release a new version of Power BI each month, alas I cannot ensure backward or future compatibility. There are no instructions for setting up the template. What version of Power BI desktop are you on?
@@erinwilson5010 - I would have thought that worked. If you ping me your email do you want to do a teams call and we can see if we can get this working for you?
Very Helpful and Love the pace you use as well (yes, we can pause, but some speak fast and don't have to rewind and rewind to understand what was said).
Thanks for the great feedback. Hope this helps you out.
Hi Ben, Absolutely brilliant!
I've rarely comment on any programming/technical videos, but all that hard work you did has massively helped me today. I even had a ticket with Microsoft about this exact issue and they only gave me vague details or reasons why it wouldn't work. I ran into some issues but mainly due to how I had things setup.
Liked and subscribed as this information has been invaluable. Keep up the great work! ⭐
Hi, thanks for the feedback!! Glad you liked it and it helped you.
Amazing work. Thank you very much for this. I had a separate flow for each of my plans and half were failing for different reasons. I am doing my first run and everything seems to be working as expected. (Fingers Crossed)
Firstly, very very useful and awesome solution to consolidate all planner plans and tasks into 1 database and the ability to visualise in 1 report!
However, I have tweaked the flow to run in sequence, 1 action after another. Managed to minimize the run-time to 2 hrs and 38 min, generating 1000++ tasks and checklist items.
The parallel branches did not work as the flow kept running for 13 hours+ without any end in sight.
Hi, thank you so much for the feedback; it's great to understand how people are using and modifying this to meet their own needs. Any chance you could export the flow to a zip and email it to me please? i'd like to try this (assuming I can import it).
@@BenHoward_PowerBI is your email address the same as the one shown when importing the .zip file onto power automate? I couldn't find your email address anywhere else.
@@beefy2390 - you'll find it on page one of the report, otherwise, yes, that one will reach me :) - thanks!
@@BenHoward_PowerBI I have just sent it. See if you have received it.
Very nice, well explained use of Power Automate and Planner
Hey Eric, glad you liked it.
Hello Everyone. I have been using this Flow for about 10 days and it is amazing. I have roughly 30 Planners that I extract and the Run time was over 6 hours. One of the areas I was able to improve is the deletion of rows. If you create a script in Excel, you can delete the rows in seconds. this literally cut hours from the flows run time.
Here is a sample:
function main(workbook: ExcelScript.Workbook) {
//Delete all rows in Plans table.
let delete_tbl = workbook.getTable("Plan_tbl");
// Delete all row(s) at index 0 from table plan_tbl
let numberOfRows = delete_tbl.getRowCount();
delete_tbl.deleteRowsAt(0, numberOfRows);
You then include the Excel Connector to run a script.
Hi @James Bucahana - thanks very much for this. Just to say I have test this and it works well, unless there are zero rows in the table, this can be taken care of with an if statement to check if the numberOfRows > 0 . So the whole code is now...
function main(workbook: ExcelScript.Workbook) {
//Delete all rows in Tasks table.
let delete_tbl = workbook.getTable("Task_tbl");
// Delete all row(s) at index 0 from table Task_tbl
let numberOfRows = delete_tbl.getRowCount();
if (numberOfRows > 0) delete_tbl.deleteRowsAt(0, numberOfRows);
}
For reference, this reduced my runtime from 18 to 7 minutes.
Thanks for your input, together the community can make many things better!
@@BenHoward_PowerBI Thanks for the feedback Ben. I will incorporate the If statement in my Flow. I hadn't run into that scenario so I didn't account for it.
Excellent video. But im puzzled why do you need a power automate since you can feed the excel file from sharepoint directly into power bi services. Am i missing something here?
Yes, you're missing how to get the data out of planner into the Excel file - that's what Power Automate does.
I am looking for the exactly same solution. This video helped me a lot. Thanks a lot Ben!
You're welcome my friend.
Hi Ben. I have an Issue where the connections are not coming up. The import is successful but connecting it to my environment doesn't work because. the connections available on the environment don't show up in my list.
@ 9:24 in the video, you can see on the menu on the left hand side, click on Data and then Connections, and in the top click on New Connection. Set you your own connections to the services required.
Hello, How can I exclude a certain plan? I am getting all plans populated and I need to include certain ones! Thanks Much for this!
It's difficult to to do this in Power Automate, but easy in Power BI. Add a Report Level filter to the Power BI report, and select the reports you need.
Hi Ben, at 8:20 add "Add Planner tasks to an Excel Online Business spreadsheet" as new. I'm trying to do this too, but I get an error "MultipleErrorsOccurred". Do you know why? The rest works!
Alas not, do you not have any further error message other than "MultipleErrorsOccurred"?
Hey Ben, thanks for putting up this flow. Incredibly helpful as I need planner info to populate into Power BI dashboards. I'm receiving the error below - am I missing something? Flow save failed with code 'MultipleErrorsOccurred' and message 'The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'NotFound'. This may indicate invalid input parameters. Error response: { "status": 404, "message": "Script not found. It may have been unshared or deleted.
clientRequestId: dfdab3a1-294f-4fef-a862-cc8cf675e122", "error":
Hi, I now use Excel Scripts to remove the files - it seems like you are getting an error regarding them - please review - ruclips.net/video/82aYZBKiwoA/видео.html
I have seen both videos about the load of data in excel and them Powerbi, but I have a problem that I do not no how to do it. I have many teams, and many planners and it takes forever to update the excel. Is it possible to tell which team I want to take a look to?
Yes, have a look at this PowerAutomate script - github.com/ben-howard/Planner-PowerBI-with-PowerAutomate/tree/main/Single%20Team
The files in the Git Hub dont match the video walkthroughs is there the original one Ben somewhere.
github.com/ben-howard/Planner-PowerBI-with-PowerAutomate/tree/main/Parallel%20with%20no%20Office%20Scripts
Ben, thanks for sharing your wisdom!
I am attempting to import the package into Power Automate and, after changing the import setup to "Create as new", I am not able to see or assign connection for each resource type. Do you have thoughts on the cause?
Hi, you need to set up connections for each resource type, you can do this from the connections tab in Power Automate, click on New Connection, and then it's best to use the search box in the top right (eg Excel) and then create your connection. Hitting refresh then back in the Power Automate import wizard on the connection will show you the new connection for the relevant connection type.
I am unfortunately stuck at the import step, where I do not have an option of a Office 365 Groups Connection. At what point in the flow is this required? Can I just manually create the power automate steps as shown in your tutorial?
see the reply thread by Angelo F to create a new flow triggered by "Office 365 Groups - Add member to group"
Hello Ben. I am confused with the report in power bi, the template was made by you? I would like to learn how to create a similar template, do you have any video or recommendation? Thanks a lot
Hi Dylan, yes, the template was made by me - why are you confused with it or the report? To learn how to create a template please review - docs.microsoft.com/en-us/power-bi/create-reports/desktop-templates
Hi Ben! First of all, thank you very much for the video and all the fields shared! I was getting a few problems with my word and you help me gracefully!
However I would like to see two things about the Labels:
1- how can I change the labels from the colors name to the labels I'm working in Teams?
2- is it possible to do a chart by label?
Thank you again for all the help.
Hi Carla, alas a limitation of the connector is that it only recognises the pre-determined label names, ie the colours. It certainly is possible to do a chart by label, you should be able to easily amend an existing chart to display the label instead of one of the other categories. A simple way to do this would be to duplicate the page in Power BI, and then amend one of the charts on the new page.
Looks realy nice but it seems this is no longer up to date with the current flow version. Now it uses a script to remove the excel lines but I can't seem to get that working properly. Do you have a seperate video talking about that?
Found my answer in the comments and will review your other video's that will explain. Thank you for this amazing build already!
Hi Ben. It's a giant and very helpful job. I have an issue. Hope you can advise. The issue is duplication in Task Sheet in the Task_id column. It causes the PowerBI error as well. I can not understand why the duplication appears. And how to remove it or fix potential problem in the power automate script.
Okay; this is typical troubleshooting and this issue can occur if you have two flows running at the same time. So, make sure this flow is not running. Then to test the script that deletes the rows in the task table, open Excel and run the correct script. This should empty the tasks table. Then, just add some stuff into the table, just a couple of task names into a couple of rows, nothing major. Keep this excel file open so you can see the task table. Run the flow. You should see the table empty, and then fill. You shouldn't get duplicate tasks, and typically it is either because the correct script is not run, or people are impatiant and start another instance of the script before the 1st instance has finished.
This is outstanding Ben, thank you so much! This is a massive time saver for me
Hi James, glad this is useful!
Thank you for the video, can this work for multiple planner at once. Like I have 300+ planner.
Yes, please watch the whole video. The planner files need to be associated with a Teams Group.
This is a great video. Is there a way to adjust the flow so that it does not look at all Teams in a tenant and only selected teams?
This would be helpful. I'm still new to Power BI and was able to follow along well enough to get this working for me (huge success! thank you!) - but the number of Teams I have is a problem. I've only created problems for myself when I try to adjust the flow to just look at one Team.
You could edit the flow and manually define a single team, or perhaps a list of teams. I've not looked at this myself.
Hi Ben, thanks for this. However, on your github page, the attached file was updated into removing rows from the tables? Should I change the action instead? Thanks very much!
Hi Roland, This solution has evolved over time, alas it is not possible to modify existing youtube. So, please watch ruclips.net/video/oWyDJmDDFfU/видео.html and then ruclips.net/video/82aYZBKiwoA/видео.html - any questions after that, please just post them and I will do my best to get back to you.
Hi Ben,
Thanks a lor for this video and you useful insights!
I was trying to draw the ppwer automate flow chat by myself but I blocked since in the "add row into a table" task I'm not able to find all the "value" dynamic plannwr contens to fill the several columns of the table.
Could you please tell me why it happens?
Thanks so much
Marco
Hi Marko, if you reach out to me via email (you'll find my email address in the Power BI template) or online etc, then we have a look via a teams call.
@@BenHoward_PowerBI Hi Ben, you are so available! However I fixed it! Thank you so much
@@showa3601 - Fantastic - great job!!
Hi Ben, having a problem running the flow (which took 3 hours - 132 plans), Seems to fail in each table branch (x4) at the very last action. In the final action of 'apply to each/List plans for a group' I'm getting the error message - 'ActionFailed. An action failed. No dependent actions succeeded'. It appears to have only failed on the very last of the actions, as scrolling through them only the last seems to have failed (141st out of 141). 140 out of 141 has a green tick... Any ideas?
Hi Stuart, alas no, typically for me when i have been developing flows like this either everything works or everything fails in terms of adding/removing data from Excel sheets.
Thank you for posting this. I am working with the original flow. I am running into an error because I am associated with 13 teams and most have a planner associated with the group. I changed 'Gourp Id' on the 'List plans for a group' & 'List plans for a group' to a specific group. But it is still running through all of my teams, which is taking over an hour, before it fails. Is there a change I can make to specify only one team?
Hi bob creek, there are 6 branches to the flow, 5 of them (all except the users branch) will list plans for a team, so you'll need to troubleshoot all 5 branches. However, if you are only interested in a single team, then I have just uploaded a flow to the repo that does just this - github.com/ben-howard/Planner-PowerBI-with-PowerAutomate/blob/main/SingleTeam-AddPlannertaskstoanExcelOnlineBusinessspreadsheet.zip . The excel and power bi file are both the same. you'll also find the associated flow map.pdf in the same repository.
Hi Ben, I’m making progress getting a single team task to load and the flow runs without issue. The only problem I’m having is the data is only being shown in the task and Team members table. Any ideas? I’ve crossed checked everything without finding any errors
Hi Allan, if you ping me on my email we can do a teams call and you can check your config. It should all work nicely, it is likely something is mis-configured.
@@BenHoward_PowerBI, thanks but I’ve sussed it. I had to change in the “list plans for a group” from the name of the Teams site to a custom value “Team ID”. When I listed the team name no raw outputs were appearing. Working a dream now I think, the proof will be if it refreshes tomorrow morning.
Hello Ben and thanks for the amazing tutorial! 👍
Would it be possible to restrain the Teams pull to only some selected Teams instead of all? I cant quite figure out the Flow there.
Thanks!
Hi, for each branch of the flow, in the List Plans for a Group, you could replace the Team ID with the specific group name you are interested in. I am pretty sure you could find a way to provide a list of team IDs, but they might need to be stored in a SharePoint List or similar and then you could read and loop through this.
Hey Ben,
This was amazingly useful. And to a lot of people it seems. Thanks a bunch!
You're more than welcome.
There seems to be an issue when configuring setup for the imported flow (8:44); I don't have the Names in the 'Import setup' pop-up, and clicking on 'Create New' just takes me to a different page where all the connections are...
Did you read the whole of the description and watch the related videos?
Learn how to configure a Microsoft Flow using Power Automate to automatically take tasks out of Microsoft Planner, import them into an Excel spreadsheet, and then automatically refresh that data into a Power BI report. The soln has been updated to use Office Scripts, so be sure to watch ruclips.net/video/oWyDJmDDFfU/видео.html and ruclips.net/video/82aYZBKiwoA/видео.html aswell.
The files to enable this are available in GitHub @ github.com/ben-howard/Planner....
@@BenHoward_PowerBI Thank you, Ben, watching the other videos seems like a good idea :)
@@alicedwonderland7733 - 🤦♂
This is great. Thank you so much Ben!
One question you might know the answer to: I have renamed the categories (red, purple etc) to actual work stream names. But I can find the value under tasks to get those names to excel. Could you have a look?
Hi, Alas if the categories are renamed it is not possible to pick up the renamed values using the existing Power Automate connector/
Hi Ben, Do you have a way to pull comments and notes from planner to power bi? Thank you for all you do!
Hi, alas comments and notes are not exposed via the flow connector.
@@BenHoward_PowerBI Thank You
Hi Ben, excellent stuff. How can we display tasks priority in power bi? I don’t see priority field in excel template.
It's a great question, unfortunately the flow action does not expose the priority, so there is no way we can show this data at the moment.
Hi Ben, is there any way which this can be worked out -Priority
Thank you so much for this! Just a quick question please, it doesnt seem to be pulling up the labels in the powerbi, is this correct? There are labels that have been created in the planner but are not seeming to sync through. Can you help please? :)
That's correct, it will only bring in the colour of the label, not any customizations. If you need these, then the best way to do it is via Power Query, ie replace Red with "urgent" or whatever your customization is.
@@BenHoward_PowerBI do you have a video by any chance or resources on how to do this please? Say if there’s just one planner it’s pulling from.
@@juniorjay911 - Hi, you replace values in Power Query - this video shows how to use Power Query to change or amend date values ruclips.net/video/_AagDlq3BnA/видео.html - or support.microsoft.com/en-au/office/replace-values-power-query-28256517-f1e9-4dc3-832f-45786e9cf721 - you open power query by clicking on the Transform button in the Power BI ribbon.
Thanks a lot for this video! Is it possible to use this process or similar to make a company level dashboard of everyone's tasks - even ones where I am not a member of the Group or Plan?
Hi, you could run the flow with a service account that has access to everything; though there are licencing implications in terms of how flow is licenced.
Hi Ben, I know it has been a while since you published this group of videos. One question -can I add the Priority by just adding a coln to the Planner Tasks excel? If so, should this be done in the Task_tbl table? Thanks
Great question, alas the priority field is not exposed via the Planner action, so it is not possible.
@@BenHoward_PowerBI Thanks for getting back to me so quickly, really appreciated. I bit of a shame, as prioritisation is rather important to planning and execution.
Very helpful, thank you! Do you have any tricks to skip failures if there isn't a plan in any group? I'm getting an error: type 'Null'. The result must be a valid array.
I have groups that don't have plans and I don't get the error, are you sure that this is what is causing it?
@@BenHoward_PowerBI I'm facing 403 Forbidden error, list plans for a group this will sometimes fail the condition for which it fail are if you are not a member of the group that you own
"statusCode":403,"headers
"message":You do not have the required permissions to access this item.
expression '@outputs('List_plans_for_a_group_3')?['body/value']' is of type 'Null'. The result must be a valid array.
I found this, around minute 9-10 she is doing a troubleshooting when this happen: ruclips.net/video/mqGFbIGpCcw/видео.html
Hello Ben, Goodday, Please i was able to follow all the steps until configuring the connection for each resource type. I am not able to select an account so i have been stuck on that step. I would really appreciate your help
Hi, have a look at the video @ 8:37. You can see in the side pane on the right hand side a button called "+ Create new". At this point you are going to create a new Microsoft Teams Connection. From memory this will open a new window, and from there you can click "+ New Connection" at the top of the screen, and then I normally search for the connection, so just type in Teams from the search box (top right). Select the Microsoft Teams connector to create the connection. The interface can also be found in the menu on the left hand side under Data | Connections.
@@BenHoward_PowerBI Thanks so much! It worked
Hello Ben, Good Day, I tried creating a new connection but it only comes up for one or two resource types. For the other resource types, even after i refresh it doesn't seem to be working. Any idea what i can do differently?
Hello Ben, I was able to figure out what the issue was. I was using just a Microsoft Teams connection for all resource types, meanwhile some were Excel Online (Business) and Planner connections.
Hey Ben! This is great work thanks for your help! Is there a way to pull in the notes field as well?
Alas not, Microsoft do not expose the Notes via the flow control
@@BenHoward_PowerBI NOOOOOOOOOOOOOOOOOOOOOOO
Thank you for this example. I am having 1 issue. All of the task are not being removed so I'm getting an error. I have duplicates. Any ideas on how I need to modify the flow to ensure that all of the existing data gets removed. So that I get a complete data refresh?
Hey, thank you for the video although I tried making your workflow but for me the loop never ends, and I get timeout error and thus no data is imported into excel. can you please guide me through it?
Thanks in advance.
Hi Ben, fantastic video, many thanks for this. Unless I've missed something, is there a way of combining multiple plans from multiple Planners/groups using the same solution please?
Hi Paul, you missed something, this is exactly what this does, it combines all plans from planner into a single report. Unless I misunderstood the question.
Hi Ben! Thanks for this wonderfull solution , I got it running, but I just need to list especific teams not all any idea of how I can do it?
Yes, this is relatively easy to get the data into the Users Table in Excel (just add in another column) and then map the team name to the new column in Excel. However, Power BI then does some cleansing on the user table as a single user may belong to many teams (basically I remove duplicates), and so I'll have to think about this and produce a new Power BI template, or you could :)
Hi Ben, very nice job! The only problem is that is not more possible to import into PowerAutomate. There is an error in the first tasks of the import operations. They said "'MultipleErrorsOccurred' and message 'The dynamic operation request to the' GetTable 'operation of the' excelonlinebusiness' API failed with status code 'NotFound'."
Please see ruclips.net/video/oWyDJmDDFfU/видео.html
Sir, How may I limit which plans are recognized in power automate?
Hi, you would need to modify the flow so that you enter the plan names or ids. Alternatively, you could place a page filter in Power BI to limit the plans you view.
The Power BI report can be viewed at app.powerbi.com/view?r=eyJrIjoiODliMzA1N2EtNzczZi00ZmViLTgyNzItYTEwMWViZDMyNTA4IiwidCI6ImI0MWI2YWQ5LTViZDAtNGNkYS05NWI3LWI0NDhkMGM2NTZiYyJ9
Note that one user has had real performance improvements (about 4x the speed) by modifying the flow to write to Microsoft Lists instead of Excel. Obviously both the flow and associated Power BI template need to be amended to use the new data sources, but if you are impacted by a slow flow then this might be a good solution for you.
Hi Ben - I am receiving an error that the content isn't available. Is there another way to access it? Thanks as always for all the expertise you provide!
@@OddPoliticalBedfellows - hi Matthew, can you be more specific as to where this error occurs?
HI Again, so my flow takes 4 hours to run, so I'd like to investigate the Microsoft List option. Are you suggesting that I create a list for each of the tables that exist in the PlannerTasks spreadsheet, and then somehow get PowerBI to load each Microsoft List?
@@peterlabermeier215 - You will need to create a list for each table (this is quite easy as you can create a list based on an Excel table so thate will create all of the columns for you). Then you will need to modify the flow to use the list, rather than the Excel sheet, and then you'll need to modify the Power BI file to read from the list rather than the Excel table. I would start with doing this for a the tasks portion of the soln initially, as this is probably what is taking the majority of the time in the flow.
Hi Ben, The flow really works well. Do you know if there's a movie to modify the flow using microsoft lists? I'm a real noob but it would be a big improvement for me because the flow is taking around allmost 3 hours to complete. I would like to schedule it somewhere around every 4 hours starting before I start work. Thank you for your help. Greetings from the Netherlands :)
Hi Ben - anyone else's flow taking an age to run? Mine is stuck on the "deleting" elements for each branch and has been now for nearly 10 mins. Don't have hardly any data in the Plans as I am trying to test it first . I am using existing groups, teams, document libraries etc. but everything else should be working correctly shouldn't it?
Sometimes this is just flow running slowly. Did it finish correctly? For ref, I have just initiated this flow and it's taken 10 mins to create 30 tasks.
Hi Ben, thank you so much for sharing this, the video and files are incredibly helpful! - I'm stuck in the part where I have to open the .pbit file; I entered the URL of the excel file but right after that I get message to "Access Web content". I tried using my credentials, but I keep getting a "We couldn't authenticate with the credentials provided-Please try again" message. I also tried using my boss credentials since she is the Global admin but keep getting same error. Do you know what could be causing this problem? Appreciate any advice I can get! Thanks again.
Hi, I assume your URL is in the following format? tenant.sharepoint.com/sites/APOGroup/Shared%20Documents/PlannerTasks.xlsx. If you can't fix it reach out to me and we can schedule a teams call to try and fix.
@@BenHoward_PowerBI Hi Ben, I was able to fix the error. Now, I'm running into another issue: all my plans are ongoing plans with ongoing tasks, and I just need to track and visualize In Progress, late and Not started tasks, I don't need the completed tasks, and I think that's why the flow is taking so much time because is listing ALL tasks. Is there a way to run the flow without the completed tasks? Appreciate any advice you can give me. Thank you!
@@dayanagelvez5610 - I don't think there is any way to build that logic into the Power Automate - but I will check.
Hi Ben! This video is amazing. Thank you! I have a silly question. At 8:48 when you're configuring the package content, I'm able to configure all except the Office 365 Groups Connection. My account doesn't pop up for that specific one. It appears that everything is connected, but I'm not sure why that one is off. Have you run into that before?
Within your powerautomate environment, click on Data on the left hand side, followed by connections, and then select the + New Connection button at the top of the screen.
Thanks so much for the video and files. Ben, it helped me a lot. Very nice, subscribing...
You're welcome!
Excellent... Congrats 🎉🎉🎉... Thanks for your tips, it's good... Greeting from Nicaragua
You are very welcome. Greetings from the UK.
Hi Ben, I am looking to only include one plan from planner. How do i only bring one through the powerautomate rather than all that i am linked to in planner?
Check out the github repository in the clip description - in there you will find a flow that will allow you to link to a single team. You many have to filter further in Power BI, or you may be able to hard code the planner Id in the power automate, but using the flow I published for a single team will be the quickest way to get started with this.
@@BenHoward_PowerBI May I ask a quick question …. How do I get the assigned to user ID to come through? I have added the ‘value assignments Assigned to user Id’ and the flow runs but doesn’t bring through the data. Have you experienced this issue?
@@bethnorbury8644 - the assignees already come through, you don't need to modify anything. Check out the User slicer in the Planner Tasks tab. Note that the tasks table has some additional columns in there which aren't directly populated, because each task can have > 1 assignee.
Thank you very much for sharing your knowledge. This video helped me a lot
Glad it was helpful!
Hi ben, thankyou for your video.
But after running the flow I found out that the checklists table is populated with many duplicate checklists, in my case it was 610 distinct checklist id's, and my table contain 4000 in it
Hi Ben,
I have run into an issue at minute 8:42 where my related sources won’t show a connection for my to select. I can only select a connection for three of the related sources but there is no connection in actions for “Microsoft Teams Connection, Office 365 Groups Connection”. Would you happen to know why this is happening?
Thanks!
Hi, you'll need to set up a new connection in flow for Microsoft Teams etc.
Hi Ben,
Thank you for this. It was very helpful. I wanted to ask if there is a way the notes in Planner could also be transferred over to Power BI? Please let me know. Thank you!
@@damonkapur2893 - alas not, Microsoft have not enabled this functionality.
Hi Ben, Thank you for getting back to my comments. I really appreciate them as they’ve helped me a lot. I have one more question and that it, the flow keeps pulling information from two different groups in Teams. In my flow I have specifically assigned it to come from ONE team but it pulls information from two teams. Making the buckets plans etc all for both the teams. Would you happen to know why this is happening?
@@damonkapur2893 - Hi Damon, given I have not seen your modified flow I feel unable to comment on it!
Hi Ben. Can you pls tell why I’m unable to see any connection to modify each resource type? Also creating connection is also not helping. Any tips pls?
Hi Ben. When I try to modify the connection for each resource there nothing to select and save. Any reason why?
It'll be because you don't have any connections set up. Open a new Power Automate tab in your browser, click in data > Connections and then New Connection (at the top)
@@BenHoward_PowerBI thank you very much. That worked. I tried importing thereafter, however I got an on the Add Planner tasks to Exel... 'Multple Error Occured'. Is there a fix for this? The other related resources seem to be fine.
Hi Ben. Great video as usual. There is one thing this leaves me pondering. Would it be possible to set up the Excel file to track how long tasks are sat in a bucket. Then report this on Power Bi. Just thinking about it as a process engineer and wanting to know average times to complete tasks from various buckets?
Hi Roy, alas the time spent in a bucket is not tracked.
@@BenHoward_PowerBI yes it is most annoying. I was thinking to time stamp the task to a bucket using excel, granted this would only give the time that the flow ran at. But at least it would be something of an idea.
@@roylewis5081 - would require some serious mod to the flow, as currently each row in Excel is removed during the flow execution.Somewhere you need to keep the history of each task. It might be possible just to append the tasks into the Excel file and do some work in Power Query to remove duplicates (along with adding in the timestamp). You'd always have the issue of deleted tasks though, you would need to remove those manually from the Excel file.
Hi Ben! I cannot seem to get the checklist is checked data to import to the Power Bi correctly. The excel file shows the correct true/false for which items are checked and not checked, but on import the entire COMPLETED dataset in Power Bi just reads "no". I've tried changing the data type to True/False for the completed data set but I get a "not allowed" message. I am not sure how to fix it, but it would be very nice to be able to see if any of these items are completed instead of having everything be grouped under "No" regardless of true value. Any help would be appreciated.
Can you reach out to me privately and we can do a teams session to see the data.
@@BenHoward_PowerBI Thank you for meeting with me and helping to resolve this issue. Its working great now!
@@333Premium333 - no problem; updated file on github, though it was working on my system. Worth keeping an eye on.
Brilliant.. THANK YOU Ben
Gotta like that :) Thanks - Ben.
Hi Ben, Thank you for this awesome video!
I just have also one more question. Is it possible to create an visible green or red Circle in the power BI Dashboard, when the status is completed or for example after the due date? I have problems with this because power Bi just accept Values for creating such a circle. But the status is just a text. Maybe you can help me here. Thank you already !
Hi Ben. Nicely done : -) Works out great. Thank you very much.
Just a few observations. Any chance that you could the priority field from planner in the next version? I tried to add it myself but it did not pull any data into the Excel table. In addition I realized that SharePoint use minus signs in their Id's which Excel translates into the start of a formula which results the "NAME" error. The error causes that Power BI to fail when refreshing data.
One workaround is to make a "Search and replace" in the entire workbook where you search for "=" (equalsign) and replace with a single quote ('). I then tride to code a macro in vba that would undertake this task it does not seem like Power Automate can handle macro-enablede workbooks (xlm-files). Please let me know if you find a sollution for this, Have a great weekend
Hi, I'm glad you like the soln. Re the priority field, alas it is not exposed via the planner "list tasks" action in Power Automate. You can use the feedback link from the gear wheel icon in Planner to feed back to Microsoft and ask for this (Microsoft are deprecating their use of user voice and I believe this is the best was to feedback at the moment). Re the minus signs in "SharePoint", I am not sure what you mean. SharePoint is simply somewhere to store the Excel file; please elaborate on your issue here with specific examples...
@@BenHoward_PowerBI Thanks for your response. I have listed a few of my tasks here. The task ID "-6VlqpkSM0eczlpof6ZQo5cAGbu8" has a minus sign in the start of the ID. Excel will assume that the Task ID is meant to be a formula. Therefore Excel will show the #NAME? error.
Task ID Task Name Bucket Name Progress Priority
HP9REq3HP06S9cl4PoZJB5cANYsf Goto Company hjemmeside Goto Administrationsopgaver Not started Medium
-6VlqpkSM0eczlpof6ZQo5cAGbu8 Flytning af Webinar - Leads Migrering af StudyNow Not started Medium
I will try to give feedback to MS regarding the priority field.
Cheers
Bo
@@bofrederiksen5022 I had the same issue. I fixed it by editing the Flow and adding an apostrophe before the field. It auto-concatenated it and writes it to Excel with the leading apostrophe on all TaskIDs which works well.
@@gregwoodard4479 Thank you! I did not Think of that solution. That solved the issue.
@@gregwoodard4479 - Are you able to share how you added the apostrophe before the field with me? I will modify my soln and add it to GitHub?
Hi Ben, the flow works really well! thank you so much. I was wondering if there is a way to fill also the field "Assigned By User Name" in the "Task_tbl". I have a need of creating a total overview including User Names but so far only managed UserID´s. Would really appreciate any hint.
The assignees are brought together in a separate table
Hi Ben, thanks for this very creative flow. Actually i am part of many Teams and there are lots of plans there. Now the flow is extracting everything from each planner. is there a way to restrict the load from any specific sharepoint / Teams to reduce the loading time ?
Hi, for each branch of the flow, in the List Plans for a Group, you could replace the Team ID with the specific group name you are interested in.
@@BenHoward_PowerBI Thanks alot! it helped me so much.
@@capricorns4 You are welcome. Please consider subscribing to the channel to be notified about new videos and keep upto date on this video.
@@BenHoward_PowerBI One more question! my flow is continuously running and repeating the data in the files. what could be the issue.
@@capricorns4 - How often have you set it to go? Have you manually started the flow when an existing one is already running?
Great video! So far the flow works, but it takes awhile. Is there a solution in which my old tasks can be preserved, and then only the information for new tasks are added from the planner to this growing database, without having to wipe the data in the excel tables? In addition, I’m running to an issue with the checklist option in which the checklist table is not populating.
I also have used the most recent and updated video involving excel scripts so that is what I’m using at the moment
@@loganedmond2932 - Hi Logan, if you don't wipe the Excel file how do you work out i) which tasks have been updated ii) which tasks have been deleted iii) which plans have been deleted. There is no easy method to solve this, and therefore the easiest soln is to remove all the existing tasks and re-import them. It is true that this is not an "elegant" soln, but it is a pragmatic and simple soln given that, due to the nature of the data, it is not really time critical (viz. a viz. transactional data) and will often only be updated at night, and therefore whether the refresh takes 2 seconds or 2 hours it is unnoticed by the end user. Re Checklists, they should work, please check your scripts.
Thanks a lot for this update! It is very useful but could you please add a Gantt chart with conditional formatting to this excellent template? Secondly, is it possible to update the PowerBI report only when a change is made in Planner? Thanks again!
Hi, you cannot initiate a flow unless you specify which plan you create or update the task in, so I'm afraid that is a non-starter. As for conditional formatting of a gantt chart, all the gantt charts I've worked with in Power BI don't support any conditional formatting, so again, alas it's a non-starter. If you are worried about the cost of running the flows, I suggest you just set the update to completely be manual, and then run this when you need to. Power BI can be set with a pro licence to refresh up to 8 times a day automatically with no additional cost, or you could manually initiate this refresh too.
Hi, Ben
Thank you so much for the video. I have one question.
When you get to the connection for each resource type portion I don't have any of the same options in the list that you are showing in the video. Is there a reason for that or am I missing a step by mistake?
I don't know why you don't see the same options - you should have at least one option to create a new connection, as I do at 8:35 in the video.
@@BenHoward_PowerBI Hi Ben, I have this very same issue, not any option showing off :/
Hi Emanuel. Did this sort? Even im not seeing any connection. if you know the solution, pls help
@@marcelgarcia498 Hi Marcel. Were you able to see the connection? If so, can you pls help?
Does anyone have any idea of why I can't change the "Planner Connection" authorization @8:13? Can't get past this point.
Is it just the planner one or all of the connections?
@@BenHoward_PowerBI It just the one for "Planner Connection". I looked under connectors and couldn't find one to add.
You need to create the connection. Watch this from about 37m onwards - 1drv.ms/v/s!AoTVCKDbj8EAkJJUhqDEyhuWf_YFCA?e=1v291E
@@BenHoward_PowerBI Thank you! That was a quick fix. Really appreciate this.
@@mistrb01 - no problem, glad you were able to move forward.
Hello Ben,
Thank you very much for sharing your knowledge and congratulations for your work!
I hope you could assist:
Could you confirm that the limitation for "Delete each row" is 256?
In the Planner task Tables with more rows than 256 we will need to delete it in the excell doc, have you any magic for this cases?
Thank you for your time you have a big fan here!!
Hi, by default it should return all rows, however there seems to be a bug in the connector. To fix this edit the flow and for each "List rows present in the nnn_tbl" Excel connector, set the top count value to be 1000000 .
@@BenHoward_PowerBI
Hi Ben,
thank you for your answer, you are right i have set pagination limit to be 1000000 and it worked, but my table still having errors with duplicate values in the id buckets, tasks.. I didnt have this problem deleting rows in excell of PlannerTasks.xlsx so it seems "Delete each" dont eliminate all the rows. By the moment I have included a "run a script " deleting the rows in your flow,
Do you think this could be insecure?
Thank you for you time
@@AndresGomez-gu2zd - Potentially it is worth manually deleting the rows in the task_tbl to remove all rows (leave the header row and a single blank row). This has worked for me in the past.
What to do when you have multiples assignees for a same task? For me it is breaking there!
Can you be more specific please, what breaks and where? The Power BI report will hand this.
Thanks for this. It seems that it will solve one thing I've been trying to do. Tried to import the flow, but the User Groups connector does not show to me, and I can't even create it, any idea wh?
Have you tried searching for and then creating the connection in Data | Connections
Thanks Ben, lovely work here. Is it possible to get the Assigned to filed populated as well currently its blank and only give the Id as dynamic content in the flow to be attached to in the Assign to column.
Hi Ben - great video and thanks for sharing to help others like myself. I have implemented the flow and it works, creating the excel sheets. However, when loading into PowerBi I get a message about duplicate entries in the ID columns e.g. Plan ID or Bucket ID. Opening the excel file shows me 47 identical entries in Plan ID. Looking at the flow for plans, I can see that 'List teams' produces a list of the 47 teams in my org. The 'Apply to each 5' and subsequent steps then writes the Plan ID to the excel file 47 times, hence the duplicated items. This conflicts with the 1:* relationship rules in PowerBi and prevents it from loading the task data. I can't see any differences from what you have shown in your video and wonder if you have any suggestions as to what I can try. Thanks again.
Hi Mel, I also had this issue today, in my case it duplicated each task 22 times. There is a definite issue with flow at the moment, but I haven't had time to investigate. If it decides to fix itself, or I find something out from MS support, I'll post it here!
I have to recreate the task table to fix this, I don't know why it started doing this, but once it was recreated my flow works correctly again.
@@BenHoward_PowerBI Hi Ben, I had the same duplicate problem yesterday and today. Have you come up with a solution yet other than recreating the task table? Thank you and I really appreciate your work. Have a great day.
@@BenHoward_PowerBI Hi Ben, can you let me know how to recreate task table? Thank you so much. I am trying to fix the duplicate issues after each Refresh.
@@HanhNguyen-mf2ji - I would open the existing excel file, and remove the table sheet. Then re-download the excel file from github, open it, and copy the table sheet from the github file into the the file you just removed the sheet from.
Hi Ben !!! I have a problem , I have the same error when I import the data that you explain , but in “next steps …” I don’t have the link of “Save as a new flow” so I can not check the path of the tables as you do ! Do u have a solution ? Thanks so much
Maybe I am not taking the right zip to import 😢
I am sure you are using the right zip file. Did you see the comment re using ?v3=false ?
Hi!!! Yes ! I add that query parameter to the URL before import the file . And after I recibe the error but it doesn’t appear the link to check the path .
Hi Ben, thanks for the video. I have one challenge with my task boards in Planner. I have one team channel for our department and then private sub-channels for different teams where i add activities for the specific team. For the sub-channels as these are set up as private groups I was only able to create the task boards via Planner on the web and then adding a tab to each sub-channel, with a link to the appropriate Planner website URL. When I run the Power Automate Flow, those plans do not pull through onto the excel template. Do you know if there is a work around for this so they would be included?
Hi, the user who runs the flow must have permissions to view the team and then the planner plan must be associated with the team, which it will be if it can be added as a tab to the channel.
@@BenHoward_PowerBI Thanks. I think that is my issue. As I added the plans directly in the Planner app there are not technnally associated with the sub-team channel team groups. It is only linked there with a tab and weblink.
@@stewartgrant3190 -Each planner plan must be assosciated with a team. The flow finds all the plans for each team; no team, no plan!
@@BenHoward_PowerBI Thanks again. Unfortunately, I have found it is one restriction with MS teams when using private sub-channels within a team group. It doesn't allow you to add multiple plans through the Planner app to different sub-channels so you cant directly link them to the team. You can only create it in MS Planner web and add the link via the website app as a tab within the sub-channel. The members you have to add manually in planner on the web so there is no actual direct link to the actual members in the sub-group. Hopefully MS come up with a solution in the future. Only work around would be to remove the sub-channels and create them as individual team groups in their own right.
@@stewartgrant3190 That's great info, thanks for sharing.
Hi Ben - How do I get my email address to show up when I do the Import Setup for the Office 365 Groups Connection?
Thanks for the great job Ben. I seems to have issue while exporting to excel with the path link. Kindly shed more light on how to work around the following error: Column 'Task ID' in Table 'Task_tbl' contains a duplicate value 'ULIKnWxby0mugRlEsggGAmUANNoq' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
Great work, thanks for sharing this Ben!
Just wondering what is the reason you run the steps for Tasks, Buckets, Assignments etc in parallel rather than in series
When I wrote it I figured it would be faster, but I don't know whether this is true or not.
@@BenHoward_PowerBI I have some real world testing to share, the flow with parallel branches is taking on average 1.5 hours to run with about 6,000 tasks. I have also split each "branch" into separate flows all triggered to run at the same time each night and the longest duration is 0.5 hours (assignments branch)
@@Bren4000 - that's great feedback, thanks for posting. The length of time the flow takes could be important if you are working in an organisation that spans multiple timezones and geographies, in which case you might need to squeeze the flow into a smaller window.
@@BenHoward_PowerBI no worries Ben, happy I can contribute. my main reason for optimising the speed is because I want to be able to trigger the flow from a button in power bi so users can refresh any changes they make to tasks in teams. it looks like excel is the bottleneck now because the separate flow branches run faster on their own and take longer when triggered together. I'll try splitting each table out into its own spreadsheet and report back.
@@Bren4000 - Cool. Be aware that with Pro you are limited to 8 refreshes per day or I think 48 with a premium account. Refreshing via a button counts as "automated".
This was really helpful! Thank you! Is there a way to do it only for a single planner instead of serching through all my teams? I have a lot of teams and plans and it takes a very long time to run the flow.
Yes of course, you would need to a new flow though. The concept is still the same, but because you know the name of the plan you can hard code it in.
@@BenHoward_PowerBIThank you!
HI! Ive been trying to make this flow on my own but Im running into some trouble getting all the info. Is there a chance you could do a video? Thank you very much in advance!
Hello Ben, Good Day. Please i wanted to ask. Is this achievable for more than one Microsoft planner.
Yes.
Hi Ben - this is super helpful and skillfully done - thank you! I was also wondering if you've had any luck creating a flow that refreshes an Excel file based on all Task detail, including checklist items, etc.? Many folks in the Microsoft community are exploring this feasibility with limited success, since apparently "Get Task Details" can't be used in a loop ("For Each"). Thanks for your thoughts and expertise on this!
Hi Matthew - thanks for the comment and the nice words contained therein. You can use the Get Task Details in a loop, I've just modified my flow to do just that, and have pulled the checklist items into a separate table. I will update the files on github when I have updated the Power BI report. I'll post another reply here once it is done so you will notified.
@@BenHoward_PowerBI Thanks so much, Ben! You're quite the Power Automate guru!
@@BenHoward_PowerBI Hi Ben - I was just wondering if you happened to tackle this - it would be of tremendous value to many folks (including myself!) I am sure. Thanks so much!
@@OddPoliticalBedfellows - Hi Matthew, just published on GitHub with Checklist items. Please see page 3 at app.powerbi.com/view?r=eyJrIjoiOTU4NzVjNmQtZjA3Zi00YzVhLWEwOTMtNzRjMDMzZTgxOTIzIiwidCI6ImI0MWI2YWQ5LTViZDAtNGNkYS05NWI3LWI0NDhkMGM2NTZiYyJ9
@@BenHoward_PowerBI Thanks so much!
Thank you. I am from Brazil.
You're welcome, thanks for watching and the kind words.
Hi Ben! This is absolutely fantastic! Thank you very much.
a very good use of office 365 tools. Gerar job! I'd like to ask if it's possible to instead take the planner of a group, take the planner of a specific team.
Everything is working great.....except, the sharpoint site I am working with contains multiple planner boards. How do I get it to connect to the correct board?
Hi, Sharepoint does not contain Microsoft Planner tasks, they are separate to sharepoint. Please re-phrase your question.
Hi Ben and thank you so much for this tutorial. You really made it understandable for newbies like me. I just have 1 problems which also occurs on all flows i made myself and flows from other sources... The flow collects the data as supposed, but it duplicates alle tasks atleast 5 times, and i really cant figure out why.... Do you have any ideas of why it duplicates alle tasks?
Hi, I am glad you liked the video and it helped you. You say you have this issue with other flows as well as mine, so it would seem to be a problem with with something other than the flow. I wonder if your plans are associated with more than one team? If you cannot find the issue in flow, to solve the issue I would remove any duplicates using Power BI, but beware; each task can exist multiple times in Power BI because it will exist for each assignment, so if two people are assigned to a single task, the task will appear twice. To remove the duplicates you need to remove tasks where the task Id and assignment ID are the same. If you are not familiar with Power Query in Power BI then feel free to contact me again.
@@BenHoward_PowerBI thank you for your fast response and help. I dont think its because of seveal teams, because i tried something like this with a private test-plan, and it still returndet the 2 test tasks several tims... i really cant figure out why.
To remove the duplicates in Power BI Query is also the only solution i can think of so far, but all the duplicates just slows down the flow a lot....
@@ABechT89 - this appears to be wierd! Is it just the tasks that are duplicating? If you watch the flow, does it fully remove ALL of the tasks from the task table in Excel. You should be able to open the Excel file whilst the flow is running to and see it empty (pretty quickly).
@@BenHoward_PowerBI I think so. I even tried deleting all rows myself before running the flow.
@@ABechT89 - I would ask someone else to have a quick review of the setup, sometimes we're too close to the issue to see it - I know this happens to me all of the time. In the meantime, if I can think of anything I'll let you know, likewise, if you manage to find your issue, please post here so that others can see the solution.
4:27 creating tasks
6:30 excel tables
9:22 Power Automat flow
This is a huge help thanks and *almost* exactly what I needed. I would like to filter the “List teams” so I only get results from some of the many teams that I am a member of. Any ideas would be appreciated.
Hi Mark, yes, this is pretty easy to do. I'll add this feature into the solution and publish it on Github over the next couple of weeks.
Updated today - please check and subscribe to github.com/ben-howard/Planner-PowerBI-with-PowerAutomate to be auto notified of future updates. The flow, Excel and Power BI template have all been updated. If you want to edit the flow then it's pretty easy. First you'll need to create a new column in the Plan table in Excel ( eg call it Team Name), and then edit the flow in the Plan branch to map the Team Name to the column. Run the flow, and then when it has been refreshed, edit the Power BI file to add in a slicer for the team name. If all that is too complex, you'll have to reimport everything.
@@BenHoward_PowerBI I do not find die solution for this problem yet - Where should i change the Flow? to only get for example one specific Team. Thank you in advance!
@@Monster1998Man - probably the easiest thing to do is to filter out the teams you don't want using Power Query - that's the simplest soln.
Hello, thank you so much for this video its helped me tremendously, however when I import my excel file into Power BI it doesn't create the singular table and the Task table doesn't have any of the computed columns I see on yours. Any ideas why this is happening?
Without seeing it I am unable to help. Can you see any data if you open Power Query?
This is absolutely brilliant
Hi Stephen, loved this feedback - thanks!
Hi Sir, I have a question for you.
How to create power bi template like you and allow other people enter URL and auto generate report?
In the Power BI Desktop application, export the file as a Template (file | export)
@@BenHoward_PowerBI I mean, how to create parameter ExcelFileWebURL and allow other enter their own URL?
@@KhanhNguyen-kg1xi - you need to use a parameter to hold the URL, and then use the parameter in Power Query when accessing the data source. When you export the file as a template, Power BI knows about the parameter and asks for it when someone opens the template. Have a look at the Power BI file that you created in the scenario covered in the video, and you'll see the parameter used and you'll be able to see it in Power Query too.
@@BenHoward_PowerBI do you have any video tutorial about this? please share with me :D
I aslo have create my own PowerBi template but when user enter thier excelfileweburl, it requires authentication permission
@@KhanhNguyen-kg1xi - docs.microsoft.com/en-us/power-query/power-query-query-parameters
Very nice and thank you for posting for us. I had some of the errors listed worked through them and was doing a manual sync to Excel. I was waiting for the sync to finish before trying the PowerBI part. I was literally watching it sync and it was almost done at 2 hours in when it just started running another instance on its own and started deleting rows while it was trying to finish. The flow is at the default of 2300 and this happened at 1940. Any idea why another instance would get kicked off?
Hi Steve, I'm glad you like the videos etc. I've no idea why the flow should start again, my runs consistenly @ 11:00pm every night! Please do post if you find the answer.
Hi Steve, This same thing has happened to me today. Did you ever figure out why this is happening?
@@koshields303 Hi Kelley. From what I remember it only happened a few times and I never knew why but I very soon after converted it to work with Microsoft Lists instead of Excel and it's been working very well since.
Hi Ben, thank you for this video. I am importing the zip file onto Power Automate, I notice that the zip file I downloaded is called Planner-PowerBI-with-PowerAutomate-main.zip, and your file is called AddPlannertaskstoanExcelOnlineSpreadsheet..So my upload failing. response Something went wrong. Please try again later.. Please can you assist correct. thank you
Hi, it won't be because the .zip file has a different name. Did you see the subsequent videos and updates to the description, such as....
To import the zip file you need to add the query parameter "v3=false" to the end of the URL when you import the file. For example from my tenant the query is make.powerautomate.com/environments/Default-746993bd-b693-45c8-8ec1-f4755c4b728a/flows/import?v3=false from ruclips.net/video/82aYZBKiwoA/видео.html
Great video, thanks for making it.
I’m giving it a shot myself and can’t seem to get the connectors working / assigned for the flow… (~8.33 in the video.) I’m still pretty new to it all, what connectors would I use if I wanted to build this manually? Or do you have a course on how to build this from scratch?
Hi Emile, it's worth ensuring you have already connected to the services and then the connections should appear, or just click on "Create New". The connection is basically your username for the M365 suite of apps, and of course this username has to have permissions to access these connections/services.
@@BenHoward_PowerBI Thanks Ben, got it working - really appreciate your quick response!
Hi, Im having the same issue. When I select create new it takes me to all my connections and all say connected including office 365. When I go back toi refresh it does nothing.
Hi Ben, thanks for this.
During the import I can't select my account (no account is showing once I click) in "Office 365 Groups Connection" can you help me to figure out why?
All the other fields I can add my account...
Hi, to help me, at which point in the video are you struggling?
@@BenHoward_PowerBI 8:35.
When selecting the connections to each resource type. All work but the last one "Office 365 Groups Connection", when clicking on the wrench nothing is shown. Not my email address nor an option to login...
@@gogogolem - what about the option to "Create new"...
@@BenHoward_PowerBI didn't work, but I solved. Sharing here in case it might help others. I created a new blank flow triggered by "Office 365 Groups - Add member to group". This brought me to a login which then appeared in the import step with your flow.
So, solved for me. Thanks again for the brilliant work.
@@gogogolem Hi Ben and Angelo, Ben great video and exactly what I was trying to do. Small problem - I had same issue as Angelo, didnt get what was your (Aneglo) workaround, if possible could you please explain more in details.
Hi Ben, thanks for sharing the files and this thorough walkthrough. I've tried to apply it but keep getting the following error in Power BI: "OLE DB or ODBC error: [DataFormat.Error] Invalid cell value '#NAME?'.. An unexpected exception occurred.". Could it be related to unsupported characters used in tasks (Hebrew in my case)? Thanks!
Hi Dan, hmm, are you able to share your file with me?
@@BenHoward_PowerBI Hi Ben, i ran into the same issue, from what i observed and checked some not all the cells where #Name? was present the cells where the IDs of Buckets, Tasks were stored, and these Ids had an '=' at the beginning , which was resulting in excel thinking there is a formula in the cell and showed an error to correct as such, i dont know if this is how the Id originally is in Office 365 or was being added at the time of writing to the sheet by the flow. Just to proceed i deleted the buckets or tasks which had this issue and re-added some of them so they would have a new Id, and so for now i was able to get the visual in the report. So i dont have a file or anything to show you, but i am hoping there is a fix for this if you have any clue
@@BenHoward_PowerBI Not sure I can, as it contains customer information :-\
@@dnesher - Well my Hebrew is non-existant so the data would be safe with me :) . The .pbit file will have a locale of English (United Kingdom), after you've got the error, can you change the Options to that the regional settings for the Current file are set to Hebrew (Isreal). This setting is used for numbers, dates and times for imported text. You'll then need to refresh the report so see if it works...
@@BenHoward_PowerBI That doesn't seem to solve the issue 😕
However, I did notice another weird behavior - when I open the excel file for editing in the desktop app, I see different data than online. On the desktop app it seems to be data from previous runs of the flow (before I modified it to focus on a specific plan), whereas on SharePoint I see the expected results.
Does this sound familiar or related in some way? Is it a cache issue perhaps? I'm not sure how to tackle this.
Hello Ben, thank you very much for the explanation, so far it is the only thing that has worked for me to obtain a report in Power BI from Planner, I want to ask you if the time of execution of the flow has any time zone configured, since I see that this is it executes at 23:00 hours but checking the execution history it did not, my time zone is (GMT-6), there is some step that I need so that the flow is executed automatically at the time that it is scheduled. Greetings.
Hi, AFAIK the 23:00 should be your local timezone, but of course feel free to change and experiment. I am sure whatever time you set is only an approximation anyway, I think Microsoft "smooth" the timings so as not to overload their servers.
Great video Ben, Thanks! Unfortunately, I am unable to open up the Power BI file you have created as I'm not on the same version and unable to update. Do you have any instructions at all on how you set up the Template or is there another way I can access this file?
Ideally, I am looking to be able to show tasks by priority, tasks by progress and tasks that are late on my Power BI report.
Hi, Microsoft release a new version of Power BI each month, alas I cannot ensure backward or future compatibility. There are no instructions for setting up the template. What version of Power BI desktop are you on?
@@BenHoward_PowerBI I am currently on the February 2023 version but should be upgrading to the October version next month.
@@erinwilson5010 - I would have thought that worked. If you ping me your email do you want to do a teams call and we can see if we can get this working for you?