Please find below a transcription of the audio portion of Ben Howard’s webinar, Automate Microsoft Planner reporting using Microsoft Power Automate and Power BI (Lesson 1), being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the recording of this webinar at your convenience.
Melanie: Hello, Melanie here with team MPUG. Welcome to Automate Microsoft Planner reporting using Microsoft Power Automate and Power BI. We invite you to join in today with questions, comments, by using the chat feature in the GoTo Webinar control panel. I will be sending out thank yous for getting involved today. And for our PMPs joining today, today’s PMI activity code is on the screen. If you’re not sure how to enter in by activity code, I have a great three step process out there on MPUG. Let me know, I can send you a link to that.
Melanie: Now I’d like to introduce our expert for today, Ben Howard. Ben has been awarded community leader for his very popular and comprehensive UK web training series, and has over 30 years of experience of implementing enterprise solution for customers worldwide. During that time, he has worked for IBM, Dell and Microsoft, as well as several smaller organizations. He now runs his own consultancy, Apple Park, providing project, project online, and power BI implementation and training services. Ben has been awarded Microsoft MVP for the last 13 years, produces video training for Pluralsight and MPUG, and has his own YouTube channel. Ben was also responsible for producing P20, an application that exports tasks from Microsoft project into outlook, which has got to be one of the most valuable apps I can think of. I will chat out Ben’s email. Please keep it, hire him if you can. He’s wonderful. Ben, a very big welcome to you. I will hand the presenter over to you now.
Ben: Okay, brilliant. Thank you. So let me show that screen. I think we are ready. Just a quick one, Melanie. Can you see that screen? It should have a big picture of some ugly guy on there.
Melanie: I’ve got it.
Ben: You got it, so thank you very much. There is a video of me going as well at the same time. So you can see me in my home working environment with a big green screen behind me. That actually is just there to really hide the clutter that exists in everybody’s office. So firstly, thank very much for joining me today from wherever you are. It’s afternoon here in the UK, so just about five o’clock, and obviously we’re going to run through till six. Thank you very much, Melanie, for the introduction. That makes me sound very old. 30 years’ worth of experience of implementing enterprise systems, so I guess that does make me pretty old in truth. So a little bit about me as we can see on the screen there.
Ben: As Melanie said, I used to work for Microsoft. I left those guys around about 2005 or ’06 time. I went out and did a lot of Microsoft project and project online consultancy, a project server as it was known then in those days. And I spent a lot of time answering questions on community forums and really finding out what people thought about the product. After that, I was awarded an MVP, Microsoft Most Valuable Professional Award. I’ve kept that for at least, well, for the last 13 years. The award cycle, it starts again pretty soon, so hopefully we’ll be awarded for another year.
Ben: If you’re comparing a photo to the video, you’ll see the photo was taken a little while back. I’ve lost a lot of hair in that time and I’ve gotten a bit grayer, so that’s me. I do some blogging on applepark.co.uk/ben-howard-blog. But in reality, a lot of that has been superseded now with YouTube videos. And I tend to put out a YouTube video instead of hard coding blogs. It’s easier to watch something than read it, so you can follow me and subscribe to my YouTube channel there, which is on the screen. As was mentioned, I produce training courses on data and power BI and actually Microsoft project for Pluralsight. So if you have a commercial subscription to Pluralsight, you can go on and grab those courses if you want.
Ben: Of course, there’s thousands of other courses on there. And if you don’t have commercial subscription, actually look out. You can sign up, I think, for 10 days for free. But also if you subscribe to me on Twitter or probably Pluralsight, Pluralsight does do free weekends, which is really consume as much as you want. I don’t really tweet much, but you can follow me. But as Melanie says, if you’ve got any questions, just ping me an email, ben@apple park.co.uk. And occasionally I post stuff on, on LinkedIn.
Ben: Okay, so that’s about me. What are we going to cover off on here? Well, I produced a Power BI report, which took data out of Microsoft Planner and put that into Power BI. Now we had an issue with that, and the issue really isn’t of my making, it’s of Microsoft making, is that Microsoft Planner … and we’ve got a couple of polls coming up shortly to ask you about Planner, et cetera, but Microsoft Planner doesn’t provide a simple way or, in fact, any way for you to report on all of your tasks across Planner. Okay? Or across all of your plans, rather.
Ben: So if I’ve got, let’s just say 30 Planner plans in my organization, there’s no single report which shows me all of the tasks that are late, or all of the ones that have been completed, or all of the ones to do with Ben Howard, or all of the ones which have a checklist item next to them. And so that was the goal of this particular solution I’m going to show you today, which is how do we combine some of the products that Microsoft give us? Effectively, not quite for free, but if we’re buying an E3 license, we get these products and how do we make best use of those licenses, which we pay a regular chunk of money to Microsoft for?
Ben: So how can we combine Power Automate if you like, or how do we use Power Automate to take data out of Planner so that we can visualize that in Power BI? And that’s really what I want to do and go through today. Or over the next couple of weeks, because we’ve got two, one hour sessions on this. And the nice thing about having two, one hour sessions, and normally I’m only given 40 minute to do this, is that actually we’ve got time to relax a little bit, show you some of the intricacies of the solution. And then that’ll allow further time for questions, and it’ll allow you to look at the solution as well. And hopefully you guys will get more out of this than just a quick 50 minute or 40 minute demo. Okay, so that’s where we’re at.
Ben: So we do have a couple of polls. I need to gauge your understanding of Planner and Power Automate, or at least I’d like to do that. So Melanie, I’m going to bring in you as my lovely assistant and say, can we have that first poll? And so poll must be closed to enable sharing. I’m not sure. Okay. We’re just collecting the responses here. I can’t see that, but when we think we’ve got enough responses, how do I …
Melanie: We are 60% voted, 63% voted. All right.
Ben: And I thank you.
Melanie: Can I share this?
Ben: You can share that.
Melanie: And that is up, so we have 25% of the none. What is Planner?
Melanie: 30% Aware, 25% have used it a little, 15%, I am using it, and 5%, I am using it for all my projects. It’s awesome.
Ben: Okay. Brilliant. So let me just repeat that because I can’t see that on my screen at the moment, so I’m not sure if everyone can see that. A quarter of people have no idea what Planner is. So that’s a good place to start because we’re members of the Microsoft project user group, so we all know what Microsoft Project is. It’s this big scheduling Windows desktop application. And one of the cousin projects, if you like, or sibling projects to Microsoft Project is project online where we publish everything up into the cloud. But the guys that produced Microsoft Project also about six to eight years ago, I guess now, maybe not that long, but they started to produce a lightweight project management tool called Planner. And Planner is really a Kanban board. Okay.
Ben: So 25% of you have no idea what Planner is, and 30% of you are aware of it. So over 50, have I’m going to say little knowledge of Planner. The remaining 45% have either used it a little bit, or we use it for some of my projects. So hopefully this will be a recap for those other people. So let’s dive into Planner and have a look at what Planner is. So if I open up a browser here, okay, just a quick confirmation through a chat off with Melanie that you can now see a browser, which is office.com.
Melanie: Yes, sir.
Ben: Thank you very much. So let’s start off with Planner. So firstly, we can see I’m logged in as a user and that user has various Microsoft subscriptions. Mine, I think is an E3 subscription. And as part of that, I get some apps. Some of them will be very familiar to you, Outlook One, Drive, Word, Excel, et cetera, cetera. As we scroll down or look down through the list, I’ve got Microsoft Project. Actually, I buy that as part of a P3 or P5 license, so that doesn’t come as part of an E3 or E5 license that majority of people have. And I also buy Power BI power. BI is part of an E5 license, but it’s not part of a P3 license.
Ben: I additionally get some other applications. Specifically, we’ll be looking at Planner and Power Automate, Microsoft lists and forms, and some other great tools that really allow us to be very productive on the Power platform. And this is really part of a power platform and I won’t go into that too much, but it’s Microsoft’s goal to create a low code stroke, no code solutions, or to enable you and me, as either consultants or semi-technical people, what I would call not developers. So Microsoft call these people like me citizen developers, so developing isn’t our main goal.
Ben: We’re never going to sit down an open visual studio to develop a sophisticated or complex application, but we’ve got enough smarts and enough knowledge. And we’re able to use YouTube and read a few documents to develop applications which solve some of the main business processes and the common business process problems that many people have in organizations, such as using forms to create a holiday request form or a vacation form. And having that form then store that data maybe in Excel and then linking that Excel file to an email and having some sort of approval process around that form. So that would be one of the scenarios for using the Power platform.
Ben: The scenario I came up with of course will involve this thing called Planner. So let’s dive into Planner, first of all, [crosstalk 00:15:38] because 25% of you have no idea what it is and 30% of you are aware of it, but perhaps that’s all that we’ve got. So first thing to say is that Planner is a browser based application. There is no desktop client Planner. There is a mobile Planner app though, as you can see here. It says, Hey, get Planner in your pocket. So for your iPhone and your Android devices, you can get a Planner app and put that on your device. We’ll just minimize that for now.
Ben: So let me take you through the interface a little bit. On the left hand side here, we’ve got the ubiquitous set of tabs, if you like, or pain or blades as sometimes these are not. I can create new plans. I can have a look at my Planner hub, which is where we are at the moment, which basically gives me an overview of the pinned plans, plans that I’ve pinned to the top here so that I can get a quick overview of the status of the tasks in those plans. And then I’ve got a list of my recent plans and of course, a list of all plans that we might have. And again, we’ve got the same few down at the left hand side.
Ben: Probably the easiest way to dig into this is to go and create a new plan. So let’s imagine that I’m going to start a new project in my organization, and I will just call this MPUG presentation plan. Okay. Now I can either add this to an existing Microsoft 365 group, which is what I’m going to want to do, or I can leave it orphaned without a group. Now, the way everything works in terms of security these days with Microsoft is everything is underpinned by a 365 group. So I’m going to add this to an existing 365 group. We will just go and add it. In an ideal world, I’d have different groups when I do this for organizations of course. We do have different groups. So I’m going to add this into my no LG group. Okay.
Ben: And then once we’ve added it into a group, we can go and create a plan. I could, of course, just go and create a new group instead, but you don’t want to have a massive proliferation of groups to plans. So think about one group, one team might be running three, four, five different plans. Okay. So, let’s go and create the plan. So this will just take a couple of seconds. Okay. So we’ve created this plan called MPUG Presentation Plan, and I’m really taken … we can minimize this now. I’m taken into the first user interface, which is called a board. So what we can do in this board is we can and just go and add new tasks. So the first thing we’ll do is we will set the presentation date.
Ben: Okay. So when I click enter, having entered the name of the task, then the task is created for me. Let’s just go and have a look at what else we can do in there by clicking on the task. Okay. So here’s the plan, set presentation date, and there’s the task. Now of course we could modify that. And I might just have to actually agree a presentation date with Melanie. So we’ve got full rights to begin to edit this. We might go and assign it to a user, so I might assign that to myself or I can add other people into the team. So let’s just go and have a look at Maria.
Ben: So Maria at the moment is not a member of this Office 365 group. So if I wanted to assign this task to Maria, we could do. And then I’m given this little prompt here, which says, Hey, Maria’s not part of this no LG group, the group that we associated the MPUG Presentation Plan with. So we will assign and add Maria into that group. Okay. So we’ve got a couple of people that are now doing this task. We can add a label to the tasks by default, out of a box. We get all of these lovely, different colored labels, which we can go and edit if we want. So instead of calling this red, I might call it urgent or something like that.
Ben: Now, again, just a heads up to this. If I edit the labels, then Microsoft doesn’t allow me to bring those labels into Power BI, which is what we’re going to do next week. So I’m just going to leave visa signed, and I’m going to, sorry, assign pink and green. Let’s just imagine they mean something to me in my organization. What else can we do? We can assign this task to different buckets. We’ll come on to buckets later. We can change for progress, say it’s not started in progress or completed. Again, this is slightly different to Microsoft Project. In Microsoft project, we’ve got anything from 1% to 100%, okay, and every granularity in between in terms of percent duration completed. Here, we either start things, it’s in progress or it’s completed.
Ben: We can choose a priority, urgent, important, medium or low. By default, medium is a piece in here. We could choose a start time and date. It’s rather a start date and we can choose a due date. And again, they’re not mandatory fields, but as a good manager, I might want say, okay, well we’ll agree to have this done by Friday. Okay. We can type in a description. So speak to Melanie about scheduling next series of MPUG seminars. Okay. And we could have various checklists on this, so I might just check schedule. And then we might just … socialize is a lovely new word, not a lovely word, but socialize. So she analyzes the schedule, as in email it around to everybody. I can put some attachments and comments on here, et cetera, et cetera. So that’s all well and good.
Ben: Once we’ve done that and we’ve populated a little more information about the task, then you can see that it appears here and we can decide what we want to see on that task. So I can do things like if I’ve checked the schedule, I could check the check box there to say that that’s been done. And we can see that one of two checklist items is completed. Okay. So far so good. Melanie, have we got any questions coming in?
Melanie: No question yet, Ben.
Ben: No questions yet. That either means one of two things in my experience. One is that I’m explaining it very well, or two, everybody’s switched off and disappeared to sleep. I’m hoping it’s the former.
Melanie: I’m learning.
Ben: You’re learning? Well, that’s good. So you see a few things are displayed on the card here. Anyway, let’s just go and add a new task in. That’s just so agree with the presentation date. And I will come up with presentation ideas. Okay. Now, one of the nice things about Planner is if something is past its due date, let’s just say I should have done this on Monday, I won’t fill in anything else, then you’ll see that we get some nice color coding. So we’re given some visual cues straight away to say, Hey, Ben, this should have been done. Okay. Now I haven’t assigned it to anybody yet, so it’s not perhaps nobody’s fault. Let’s just go and assign that to myself.
Ben: And you see, when I click on the assign, let’s just do that. We can do it here straight away, but the two current members of that team are pulled up for me. So again, we’re trying to make things a lot easier for users. So we’ll assign that to Ben. We’ll give that a bit of an important priority. And we’ll just say that it’s in progress again so that you can see what appears just on the main piece of the card here, just so it gives us those visual cues.
Ben: Okay. So, so far so good. I’ve got a couple of tasks. Now on this view, we’ve got the ability to create buckets. Now just think of the bucket as a categorization of a task, and you can categorize your buckets and you create new buckets with any name that you want. Now, if we think about it, we’ve already got the categorization of progress and we’ve got the categorization of priority. Okay. We’re also able to group not only by bucket, but who tasks are assigned to. So I can group and show Maria’s tasks and Ben Howard’s tasks. We can have a look at progress so we can see which tasks have not been started or are in progress or are completed. We can view by due date. So again, we can just have a look at late tasks, tasks due this week, tasks that are upcoming, et cetera. Labels, so we can see all the pink stuff, the green stuff, the red, yellow, et cetera, and priority. Okay.
Ben: So we already have some neat ways to group categorize our data and those categories are there for us. But what the buckets allow us to do is define our own. Okay. So maybe we’ve got a bucket where we want to give some work to our marketing department and we have another one for our sales department. Okay, marketing and sales being different. We could come up with presentation ideas to a marketing department and agree of a presentation date to the sales department. Yeah. And to do bucket, maybe I just don’t want that. Right? So maybe that’s not a useful bucket. We could go and delete it, and we’ve just got now a couple of buckets, so marketing sales.
Ben: And typically we might have HR or something like that. And as you can see, we can move these buckets to the left and just change the order of what’s happening. Okay. So that planner for us in many respects, that’s at least creating tasks and hopefully gives those people who either have an awareness of Planner, or you are one of a 25% that said, Hey, I’ve got no idea what Planner is, hopefully it gives you an idea of Planner. Okay.
Ben: So having created some tasks, then we’ve got the ability within Planner to click on this view here, which is called charts. And charts gives us some visualizations, like Power BI that show us the status of those tasks. So really they’re visualizing in numbers what we might be grouping by here or filtering by, or just looking at by members. So again, in charts, we have a nice little status chart here, which says, well, we’ve got one task, which is not started, one is late, none have been completed. Let me just go on and add in a couple of more tasks in here. So let’s just add in … we’ll call task one, but we will set task one to be completed. We’ll just ping that out there. And yeah, that will do it.
Ben: So that gives us a little more. We’ve now got three tasks. One of which is completed, one of which is late, none of which we’re in progress, and one of which is started. In fact, the in progress one is quite interesting because that task that we looked at is in progress, okay, because I marked it in progress. But notice because today’s date is beyond the due date, it marks it as late. Okay. So you can have tasks that are late and in progress, which is quite interesting. So we’ve got a total of three tasks and three statuses here. Okay. Those are nicely split across different buckets, and we have again, different priorities. And again, the legend here defines the status of that task, whether it’s completed or not started, et cetera.
Ben: Down here, we can see which tasks have been assigned to different resources. And in fact, which ones of course are unassigned. Okay. Again, we’ve got all of the filtering on here that you would expect over on the right hand side. And in fact, we can just go and add tasks from within this area as well. So we’ll just add a task called task, or follow up set CRM system for follow up. We’ll add that task. And if it’s not part of marketing, actually, that should be part of my sales bucket. Okay. So we can do all of this nice work that’s certainly not started, but the due date, we’ll set that to be the 25th. Yeah.
Ben: Something else, which we didn’t talk about, but that we can talk about is the comments here. So if I put a comment in here and say, let’s assign this to Maria, so let’s just go and make something in here. We say, hi, Maria, can you [inaudible 00:30:49] me? Oh, and this is completed and add in the CRM sales campaign document, okay, because she can go in, then once she’s done this and added the document to this item here, we’ll send that off. Maria now is part of the collaboration of this. And when she replies to my comment here, logged in as Maria, I’m going to be notified of that. Okay. So those things are one of the things … if we think about Microsoft project, one of the things, because it’s an old application, it doesn’t do very well is collaborate. One of the things Microsoft have worked on with modern applications is to really get that collaboration flowing. Okay. So, that’s a little bit of the interface.
Ben: What else can we do? Well, many people these days work in teams, so let me just open up Microsoft Teams. So even if you just get this far, there’s probably a lot of value in Planner. Now Planner’s never going to change or replace Microsoft Project. And the reason for that is that Planner is a task management tool. Whereas, Microsoft Project is a schedule management tool. So one of the things you can’t do in Planners, you can’t say, Hey, I’m going to assign this task to Maria. It’s going to start on Monday and finish on Friday, or that’s for start date and that’s my anticipated due date. But what we can’t do is say 40 hours worth of work for Maria in there, or there’s two days worth of effort. In the same way, you can do that in project, but you can’t do it in Planner. That field does not exist.
Ben: And so Planners never going to replace the scheduling management tool that is Microsoft Project. But I find that these days, a lot of people will use a combination of Project and Planner. And they’ll use Planner for the detailed task management, and Microsoft Project for the schedule management and for the resource management. Okay. So here’s teams and there’s a lovely Power BI report, which is not where we are today. We said we had a team called … What was that team? We haven’t got a team called Noel, so let’s just go and create a team called Noel. We’ll create that from a group. We should have a Microsoft 365 group in there. We called it Noel G. So let’s just create a team from there because that’s where our plan was.
Ben: Now, one of the nice things that Planner does and the new project web does is it plays nicely in Teams. So we can come in here and I should be able to search for the Planner plan, or the Planner application, Planner. I did this only today. Now I did it for project. Okay. Well, let’s add in tasks by Planner. Use an existing plan from this system, MPUG presentation plan. So we’ll take the MPUG presentation plan and then we’ll use this new app called Tasks by Planner. And we can then view the Planner plan in Teams, so we have full teams integration. Let me just expand that tab. Okay. So we’ve got the full teams integration of Planner into Teams. Okay. So as I add in new tasks into here, they appear in Planner, or I could just work directly in Planner.
Ben: Let’s just go and add a new task into HR. And this will be on board, new employees, of course. Okay. That’s that task added there. As I go back to here, let me just quickly refresh this. Then you’ll see, there’s that task up here in Planner. So Teams, of course, it’s just an interface into Planner, as you would expect. The other nice thing I have in Teams is I can take that same app, okay, which was called Tasks by Planner and To Do, and I can pin it to my apps here, by right-clicking and right-clocking here. And then when we click here, then what this shows me is it shows me all of the tasks across all of the plans, but it shows them for me as in Ben Howard. Okay? So here’s my come up with presentation ideas in the MPUG presentation plan. Here’s my agree of a presentation date in the MPUG presentation plan here.
Ben: And again, we get the nice same red eye catching icons to tell me that things are late. And if I wanted to, I could just edit this task directly from within this little app here. And we might come and just say, right, well, that’s now being completed. Okay. And we might type in some messages or something else or add an attachment, et cetera. Okay. And then that task, of course, just disappears because this filter on here is just showing all active tasks. So Planner and Teams really play very well together. And they allow me as a single user to see all of my tasks across the different plans that I’ve got.
Ben: Now notice, whilst we’re here, I realize that I do need to speed up a little bit, that this is called Tasks by Planner and To Do. Okay. So I’ve got to do tasks in here. So I might not have any tasks that are part of Planner, but this just might be my personal tasks. Go and walk the dog at lunchtime, or remember to eat healthily today or do my expenses or whatever. Important ones are obviously ones that are just marked as important, planned to ones that actually have a due date associated with them. Okay.
Ben: And we can get directly then to those plans from here. So if I’m working on a plan called Ben and Maria, and I’m interested in looking at Maria’s objectives, then we can go in and do all of that as well. And from here again, we can go and see the board and the charts and all of the data in Planner. Okay. Now, one last thing before I come off on this, and I’ll ask if we’ve got any further questions, this says Tasks by Planner and To Do. It is Microsoft status objectives, so this will also be tasks by Project Planner and To Do. So sometime in future I expect to see the integration of project for the web tasks in here as well. Okay, good. That’s me [inaudible 00:38:34] done on Planner. That was a brief introduction. Melanie, any questions coming in at all?
Melanie: We have no questions so far.
Ben: Great. Okay, good. I’ve just worked out I can expand the questions tab and having expanded that, I can see that myself. Okay. So let’s get on to the meat of this. We’ve got some plans. If I wanted to export those today, my only way of exporting those is to click on the ellipses within Planner here and say export plan to Excel, which is a bit nasty. There are some YouTube videos upon how to do that and bring those into Power BI. And I produced most of those as well, but this goes beyond there.
Ben: What I want to do is bring those plans or those tasks into an Excel file. So we can do that by using this other component we’ve got, which is called Power Automate, okay. Now Power Automate used to be known as Flow. And you can see up here, it’s called … where I’m based in emea of course, it’s emea.flow.microsoft.com. Yours might be us.flow.microsoft.com or something similar. What we’re able to do is go ahead and create a flow. So what is a flow? A flow is a way of automating the connection of systems together and the transfer of data of those systems. Okay. Now, when you first start Automate … in fact, can we do the next poll please, which is just who knows anything about Power Automate? We should have done that before we started this, but luckily somebody’s just brought me a cup of tea, so could [inaudible 00:40:20] while we do that poll, please, Melanie?
Melanie: I will launch that poll. We also had a question come in, in the meantime about adding the plan from Planner to Teams. Poll is coming up.
Ben: Poll’s coming up. While people view the poll, do you want to read the question out? We’ll see if we can answer that.
Melanie: So the question was, how do I add [crosstalk 00:40:49]
Ben: How do I add the plan from Planner to Teams? Okay, let’s do that again. So let’s just go into Teams. And it doesn’t matter whether you’re on the full fat client of Teams, if you like [inaudible 00:41:01] browser.
Melanie: And your screen will not come up until the poll completes.
Ben: Okay, great. Sorry. Sorry. Thank you for doing that. How are we doing on that poll then?
Melanie: OK. I will close this out. We had 61% voted.
Melanie: And then I will share the results with the audience here. So again, rate your experience with Power Automate. 35% have no experience with Power Automate, 50% are aware of it, and 15 have a couple flows running, and we have 0% that can’t live without Power Automate.
Ben: Here’s a brief story, so can’t live without Power Automate. I do some work for a Manchester airport group, which runs Manchester airport near me, and a couple of other airports in the UK. They are big time into Power Automate, big shout out for those guys there. They have got rid of a load of paper processes, and they’ve now got Power Automate on their emergency services, i.e, their fire engines, the guys that go and check the runways, people who are picking up foreign objects, FOD from aprons and stuff like that. They have Power Automate and the security. They are just massive on Power Automate. They’ve changed the way they do business and streamlined a lot of their businesses. Okay. So that’s Power Automate, a little bit of a background on that. A real life case study, if you like.
Ben: Let’s answer the question, which was, how do I add the plan from Planner to Teams? Okay. Well, first of all, you choose your team. Okay. And I’ve got a team here called G’s. And if I go into G’s, then I click on the little icon at the top here and it says, add a tab. So the tab that I’m going to look for is Tasks by Planner and To Do. Stay organized across all your teams and individual tasks. We can add that in, and it’ll say, do you want to create a new plan or use an existing plan? Looks like I don’t have any existing plans. So I would have to go, and in this case, go and create a new plan, give it a name, da, da, da. And that’s how we do it. Okay.
Ben: But remember this is recorded, so you can come back and watch that and see how I did it before for the no LG team that I had here. And then if I want see my plans, so that’s the plans for a team, I might be in different teams. If I want to see all of my tasks across multiple plans, then I can go into the tab bar here. And again, look for the same, excuse me, look for the same application, Tasks by Planner and To Do, and just then that pops into my list here. And then I always right click and I pin it. And the other nice thing with this is that you can begin to in that client version of this, you can … in fact, you might be able to do it in this version. You can. No, you can’t. No, you can just expand that, pop it out into another window and it works quite nicely there. So hopefully that’s both of those things.
Ben: But back to Power Automated. So what does Power Automate allow you to do? It allows you to, in a low code stroke, no code way, take data from one system and put it into another system. So for example, you could create a Microsoft form, which is a holiday request form. You could have that holiday request form automatically fill out an Excel spreadsheet and everybody … or add into an Excel spreadsheet. And then that Excel spreadsheet or that form then sends you a summary of what’s being asked in terms of a holiday. And then you could approve or reject that holiday or vacation request. And if you approve it, then the holidays decremented from the user’s holiday availability. And if it’s rejected, then of course the holiday isn’t decremented. So that’s what Power Automate can do in a way.
Ben: Now to get an idea of what it can do, you just go and have a look at the templates. Okay. And let’s just go and search for Planner to see what we can do here. Okay. So Planner is this icon, of course. So you could say if you wanted to create a task in Planner for Microsoft Forms and post a message in Teams. So an example of this could be you’re running a help desk, you have a help desk form. So somebody fills out help desk form in your organization. That then goes and creates a task in Planner and posts a message in the relevant team. That’s pretty useful. You could create Planner tasks for flagged emails in Office 365. You just go through this and find something that might be useful to you. If you’re big into SharePoint, then you can do SharePoint.
Ben: What I chose to do instead, or what I wanted to do is I wanted to go through all of the plans in Planner and write all of that data into Excel. So I had look through it. There weren’t any useful flows. So in fact, we’ll go and create the flow. We’ll start to create the flow that we wanted to do. Now, I said this was going to be … There’s different types of flow, automated cloud flow, instant cloud flow, schedule cloud flow, desktop flow, business process. The one I chose was a scheduled cloud flow because it’s just going to run up in the cloud, so we’ll start to build this. It doesn’t really matter what I called it, so we’ll say Planner to Excel to Power BI.
Ben: And I run my flow … mine runs every night at about midnight, actually. So that would be 12:00 AM, wouldn’t it? Okay. And I just want to repeat that every day. Okay. So you don’t want this repeating every minute or every second. Ultimately, somehow you pay for your flows, right? And so you don’t want to be chunking up and spending a load of money with Microsoft, but you do want spend sufficient … you get value out of your E3 licenses. So I run this every day, which is fine. And then I just said, right, let’s go and create the flow. And it puts you into this flow designer. Okay. This is all web based and it can become massively complex. You can use flows for many things.
Ben: So I’m just going to add in a new step here. So we know that our flow runs every day. And then once that flow runs, what do I want to do? Okay, well, you’ve got lots of solutions that you can do with things. You can do lots of things around controlling, so you can run some loops, et cetera. Let’s just have a look. You’ve got some standard things, some standard value things. So we’ve got some things that we can do with Excel online, or email some people, or write some things into Teams or notifications, or send things to Office 365 or One Drive for business. We’ve got some premium items, so we might want to do some things with SQL server. Okay. We might query some SQL server or do some other things.
Ben: You can download and write your own custom connections as well. What I did was I went to Excel online and ultimately what I wanted to do was add a row into Excel. So I said, I’d like to add a row into a table. This is where I started and what Power BI, or not what Power BI, but what Power Automate then asked you to do is say, okay, give me the location of this, where you’re storing this Excel file. Okay. And you can see it goes through basically a load of SharePoint groups or SharePoint items themselves. So what we need is we need a SharePoint folder.
Ben: So I’m going to go into SharePoint here. And then we’ve got into SharePoint just by clicking on the apps here and clicking on SharePoint. And we’ll go and create a new site. This will be a team site. We’ll just call it MPUG presentation, presentation. Okay. It’s valid, the name’s available. That’s all okay. I don’t need to worry too much about any of that. I’ll click next. I’m not going to worry about permissions for SharePoint at the moment. I will be the owner of it and that’s fine.
Ben: Now, the thing about SharePoint is that maybe later I can begin to store some documents in SharePoint. Now I chose to store my Planner tasks in Excel because Excel’s really easy and it’s free, but I could have also updated a SQL server or something else with those tasks. So what I did was I created an Excel file. Now, everything I’m going to show you is actually available on GitHub. And as you watch and miss back, you can go and note down this link here, github.com/ben-howardplanner-powerBI-with-powerautomator. We’ve got all of the files up here, which will allow you to do this and allow you to build this solution. But one of the files I’ve got is called Planner Tasks.xlsx. Let me just download that. Okay. Well, let’s open it up to have a look at that, first of all.
Ben: Okay. Eight minutes left today, so we’ll get partway through this, but not all the way through. It’s fine. So this file, which you can go and download, literally consists of six tabs. And each tab here, plans, buckets, tasks, assignment, team members, and checklists, has a table in there. You see, this table is called plan table, plan_tbl., and it literally lists the plan ID and the plan name. We have a similar thing for buckets. You know what buckets are now. We have a plan ID, bucket ID, bucket name. We have tasks. Tasks is quite big. We’ve got created by, created by, user ID and title, so that’s quite a big table. Right at the end, we have the individual task ID. We have assignments and ID, task ID, user assignment ID. We have team members, those people we’ve assigned to tasks, and we have checklists. So that’s for that, our nice Planner tasks.xlsx XL file.
Ben: So I’ll close that down and we’ll come out of GitHub and we will just upload a file. We’ll upload that file to here. So files should be in my downloads if all went well. Yeah, there it is, planner tasks. Bang, open that. We’ll upload that file to here. Yes, great. That’s well and good. Okay, so that file’s loaded. We should now be able to reference that file in our flow. So let me just refresh. Let’s just add a row into a table. Let’s just go and see if we’ve got the input group in here, not yet. Let me close this out. So sometimes, of course, this takes a while. Let’s delete, delete, delete, delete, delete. Okay, add a new step, Excel. And we will add a row into a table. Okay, MPUG presentations, so that’s my group. That’s effectively what we’ve just created in there.
Ben: Then it says, select the document library. Well, I’ve only got one document library, it’s called documents. Then it says, what Excel file do you want? Well, I want Planner Tasks. And then it says, select a table that we want to write into. Okay. In this case, we might choose plan table. And that’s effectively the crux of what we end up doing. Okay. We’re writing data into a row, but before we write data into a row, actually, I need to go and get the list of the plans. So how do I get the list of the plans? Well, I get the list of the plans by getting the list of the teams. Okay. So one of the things I need to do is list all of the teams. And then once we’ve listed all of the teams, we’ve got another option, which is, we can then go and … if we search for Planner, we can list the plans effectively for a group. Okay.
Ben: Now this is where it starts to get a little bit complex. So every night at 11 o’clock or 12 o’clock, I list the teams. And then for each team, I’m going to list the plans for that particular group. And it says, give me the ID of the group. Well, the ID that we want is going to be A custom value, okay, because somebody might have created a new group or a new plan, like I’ve just created, so we would enter a custom value here. And it says, really what we want is the team ID. Okay, the unique ID of the Office 365 group, so that should work. So, that gives us a team’s list.
Ben: And then what do we do for every plan in the group? Well, we then go and we want to list out all of the tasks. So again, in Planner, we can go and list the … we want to list the tasks, so that would work for that, a greater task list of tasks. Let me just search for list on here. List buckets, list task details, list tasks. There we go, list tasks. And we really begin to build this up. What task do we want? What do we want to list this for? Well, we pick up the custom value here and we begin to list this for the team ID. And then for the plan ID, we would be the … enter custom value. It would be the idea of a plan. And we begin to up our flow in that way.
Ben: And ultimately then once we’ve listed the tasks, then for each tasks, we add the row into the table. So again, this is where we do Excel. We’ll just do the first one and we’ll see if we can get this to work. We add a row into the table, the table that we’re going to do. This is effectively a repeat of what we did before. I’m going to get rid of that one down there, so this is input presentation. We’ve only got the one document library. The file is called Planner. And because we’re listing tasks, we’ll put this in the task table. Okay. Oh, and then, because we’ve got that list of tasks in the task table, we need to populate that task table with something.
Ben: So in the title, I’ll do the title value. And let’s just go into here again. We can put in the start time, the created time, et cetera, et cetera. Towards the bottom, I’m just going to put in the ID, so the ID of the task. Okay. That should be good enough. This one we can delete. Okay. So let’s just see if this will run. Okay. I can run it manually and we’ll see what happens. I’m going to save this and then we will finish. We’ll probably go a couple of minutes over, Melanie, but that should be okay. What we’ll do is we’ll go back to input presentation here. We’ll just open up Planner tasks in here. I’ll put that on a smaller screen there. We’ll go to the tasks piece, because this is the table we said we might write if this works properly. This is what we’ll write into. We’ll go back to flow here. I’ll just put that on the left hand side. Okay. And then we’ll test this flow.
Ben: So we’ll say let’s just run this manually, but test run that flow now. So that’s going to kick this flow off and we should be able to see it running. And if I’ve done it right, then what we’ll see is this starting to get populated by tasks. There it is, hey, hey. We’ve got the titles coming in automatically as this flow is running. And if we click on this flow, then we can see it’s progress for running, so it’s been running 21, 22 seconds. And you can see it’s just going through and filling that in. Okay.
Ben: I will leave up there for now. The end result of this is a flow which looks a bit like this. So I had one, obviously on a different screen. In the UK, we say this is our Blue Peter moment. Okay. And let me just take you through what we do. First of all, I just created this one branch here, but ultimately the way I had to do this, because it runs every night at 11 o’clock, is for all of the tasks, initially we’d go through and we’d delete all of the tasks in the row. And then we go through and we [inaudible 01:00:37] all of the tasks in the table. And then we go through and we add them back in, which is what I’ve just done here. But we do that for tasks, buckets, plans, assignments, users, and then for checklist items as well.
Ben: And that gives us an Excel file that is then fully populated. If we go back into SharePoint here, the one that runs every night for me is this one on APO group, it gives me this Excel file, which is fully populated each night with a whole host of tasks. Let’s just [inaudible 01:01:17] show. There we go. So this runs each night and just populates that set of tasks. Okay. So, that’s what flow is doing for us. It runs consistently every night. It takes tasks out of Planner and it puts them into Excel for me. And what that allows me to do later on is go on query about Excel file from Power BI. Okay, two minutes past six. I know we’ve covered a lot of information there. I don’t think we have any further questions, do we, Melanie?
Melanie: We do not, but if someone has a question and they would like to share it with us for the next session, Ben will be back with us next week to go farther with us, so please save those for us. Ben, thank you so much. It was a great session. A big thank you to our community as well for joining in today on this meeting. I’ll point out to you, don’t miss … register for Ben’s next session on March 16th. The activity ID for today for our PMPs, it’s a technical PDU and it’s on the screen. Also, please consider registering for Excel, I live in Excel. Quick Tips to Help You be More Productive by Mike Thomas, that is coming up. I’ll be sharing a link shortly with this recording. We went through a lot today, so I think that would valuable to take a look at. I’ll also send you a survey. If you have any questions you want to share with us there, or feedback, we’d love to hear it. So Ben, thank you again. This was super and we will look forward to seeing you next week.
Ben: Look forward to it. Thank you.
Melanie: I will leave the PDU up on the screen in case someone missed it. Thank you again for learning with us today.