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 2), 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 Power Automate. Welcome to automate Microsoft Planner reporting using Microsoft Power Automate and Power BI. This is lesson two, so we hope you’re able to join us for lesson one of this session. We invite you to join in today with the chat feature in the go to webinar control panel. I have it shown here on the screen. You can pull that right out from the go to webinar control panel and expand it so it’s easier to type in there. I will be sending out thank yous for those of you getting involved today. So please, we have a very talented presenter, ask him some questions. Let’s stress him out today. Sorry, Ben. For our PMPs joining today, the PMI activity code is on the screen.
Melanie: And now I will introduce our expert, Ben Howard. Ben was awarded community leader for his very popular and comprehensive UK web training series and has over 30 years of experience of implementing enterprise solutions for customers worldwide. Ben has also been awarded Microsoft MVP for the last 13 years, particularly important to our Microsoft community. I’ll chat out his contact during the session. Please reach out to him. Ben, a very big welcome to you again. Thank you for coming back.
Ben: Thank you. Thank you very much for having me again. Let’s just make sure I can get a good monitor up here.
Melanie: I see your slides.
Ben: Brilliant. Thanks very much. Thanks, Melanie. So welcome back, everybody. Welcome back from a … just a moment. Bit of a rainy and dank UK. So welcome wherever you are in the world. Just as a quick recap. Melanie did a brilliant introduction. That’s obviously me on the screen. Maybe, obviously, maybe not, but I don’t tend to put photos of other people up there. Those are my contact details. If you want to get a hold of me after this session. Just as a quick recap, for those of you who didn’t manage to make part one and maybe you’re thinking, “Hey, what’s all this about?” What this is about is really taking data out of Microsoft Planner. Let me just put a laser point drop here. Taking data out of Microsoft Planner. Their Power Automate. Sticking that into some repository. So we could have chosen SQL server.
Ben: Somebody contacted me and said, “Hey, I’ve actually done this. Instead, in Microsoft Excel, I’ve done it in Microsoft Lists, and it runs a lot faster by the way, Ben.” I said, “That’s really good.” So if you are struggling, if you like what you see here, but actually performance is an issue, you might decide to go and stick this in Microsoft Lists. Or as I say, you could stick it in SQL server. It would require changes of course, to this portion of the flow, to change for data source or export data source. But we store the data then out of Planner into some intermediary file. I chose Excel because it’s free and it’s easy. And it’s part of most people’s E3 license. And then we go and query about from Power BI. So in part one, we started doing first three pieces.
Ben: So I’ll have a quick recap of that and we’ll have a look at the flow in particular, and then we’ll open up Power BI and we’ll have a look at how we kind of visualize the data. Okay. So, keep your questions coming in. If you have any and I will, I answer those. I can’t present [inaudible 00:04:02] questions at the same time. I’m not that talented. Where did we get to last time? So last time what we had is we had this flow, which I created just to show you that. We’ll edit that. It was very simple flow. It was the bones of the flow, if you like, where we ran this every day. We listed, went out and listed the teams in our office groups. And then for each team, we listed the plans for that particular group. And then for each plan, we listed the tasks and for each task, then we wrote those task values, those task values, into a table.
Ben: And I went through where you got this table from and where you could put it. What that actually gave us was a table that now looks a bit like, well, not a bit like this, but looks absolutely like this. Okay? So the flow runs and it then writes data into this table. So one of the issues that I had with this flow and we’ll see that if I go back to my flow, it runs every night. So this is when we created it. And then it’s run every day since then. So it’s run multiple times.
Ben: And I mentioned this in the first session, but one of the issues of course is for, if it runs multiple times, we’re just adding data into the table. And so you can see that this particular task called another brick in the wall, two points if anybody can remember which band produced that great single, was when I was at home, another brick in the wall, it will have the same task ID, so it’s duplicated somewhat. So this flow as it exists isn’t sufficient. I demonstrated it last week just to give you the idea of what it could do.
Ben: What we need to do is go and have a look at the full flow. So let’s just go and have a look at this flow, which is the full flow, which you’ve got access to. Again, this just runs every night at midnight. If we edit that, you’ll see it’s a little more sophisticated, a little more complex, if you like, than the one I created. Let me just go through what it does. So the same, what it does, first of all, is it goes to your Excel document and it says, “Hey, let’s go and list all the rows in the current table.” So the Excel document, the Excel file where I’m storing this data, which we just closed down. So let’s just open it up. Should be here in Planner. So let’s just go to that file. Let me get rid of the filter.
Ben: [inaudible 00:07:17]. So let’s go to that file. Okay. List all of the rows in that current table and then go and delete each row. So, first thing is it does, is it gets rid of all of the data out of the table. So we end up with no … we know we have no data. Then we go and do effectively what we did before. We list for teams. And then for each team, we list for plans. And then for each plan we list for tasks. And then for each task we add a row into a table. Okay. And we map these particular values. Okay. So the fields here, title, start date, time, created date time, due date time, they are the columns that are available in the table for me to write to. Okay. So that’s why it’s important to have this table already available in the system.
Ben: And as a table, remember, we gave this a name. Task underscore table, so that when we do come into … thank you. When we do come into Power Automate, then I can choose the table that I’m going to write to. And I can say, “Okay, the value, the title, okay, which we get from the content here.” So the dynamic content I can search for title, then I can map the value title that the Power Automate action is giving me. I can map that and I can say, “Hey, that wants to map to the title in the tasks table.” So that’s effectively what we do. So we go through a map all at once. I mentioned that there are some things that really could be got rid of that this, the action to query the tasks or to list for tasks brings back things like assigned to user ID.
Ben: Well, more than one user can be assigned to a task. So some of those things don’t work and we don’t bring back the values, but I don’t get paid for this stuff. So I’ve not tidy these up. You could do if you want, but there’s no issues in leaving them as they are. Okay. So that’s what we do. We go through and for every task we write for task details into the tasks table. We do the very same thing with the buckets table. And if we move across here, we then do the very same thing into the plans table.
Ben: We do the very same thing into the assignments table, and we do the very same thing into the user’s table. And finally, we also do the very same thing in the checklist table. So we’ve got several tables or six tables, I think, but that we’re working with in our Excel file. Okay. So that’s what the flow does. Now, if you were coming at this from scratch, what would you do? Okay. That, we’ll come back onto that. Once we’ve got the data in the flow, then of course we can go and view it in Power BI. Now, this is app.powerbi and my Planner reports. Let’s just have a look in here. I just search for Planner.
Ben: That should be in Planner report. The one, the one which is updated each night. This is the latest one. This is what the end result looks like. Okay? So we can come in and query my plans by status. So all my plans and have a look at all of the tasks in here. So these are all the plans that I have in Planner. And I can select a particular plan such as I’ve done here called MPUG presentation plan, which is one we created last week. And I can get a list of the … there’s actually six tasks. So four remaining. Two are complete, one is late. You can see the split down there, and this is who they’re effectively assigned to. And remember one task can be assigned to multiple people. So this task here, agree the presentation date, it’s the same task, but it’s assigned to both Ben Howard and this, my system called [inaudible 00:12:04].
Ben: Okay. So fairly easy to track and view the data. If we just did want to remind ourself of the Planner plan, we can go into the Planner hub and here’s the MPUG presentation plan. And if we look at charts, we can see that we’ve got four tasks left of which two are completed. One’s late, zero in progress and three are not started. So that should match up very nicely to this sort of visualization that we’re seeing here. Okay. Which it does. So that’s good. So how do we get to this end result on app.powerbi.com? Well, as I’ve already articulated and talked about previously, these slides or sorry, this solution is available on GitHub. So, if you go and search for Planner Power BI with Power Automate, there’s the URL at the top there, then you get to this solution. And one of the items on the solution of course, is the Power BI file. So let’s just open that up and point that to the right, to the right Excel fILE. Okay. So let me download this first of all, so that you can see me doing this from scratch.
Ben: Okay. It’s not telling me it’s downloading that, but let’s just hope that that is. Oh, I think it’s decided to download it and open it all at the same time as Power BI has just started to open. Now, if this isn’t going to present on the right screen, I’m just going to close this down and start it again, and that in truth is happening. So, let’s cancel out of there. Let me just put a new version of Power BI on the screen, if you like, and from my downloads, let me … Sorry, people. Okay. Here’s the download that we’ve just downloaded Planner Report 1.0.pbit. So if you did this from last week, actually I’ve updated it slightly. So, it’s always worth coming and having a look. It’s been updated the Power BI template file and that gets updated fairly regularly.
Ben: So, from inside of Power BI, I can say import and I’ll import Power BI template. There’s a Planner report template. Okay. Now I tend to run a couple of versions of Power BI on my desktop and this is the issue I’m seeing here, but I’m pretty sure the existing version can cope with this. Okay. When you open up the template, there’s a little bit of text that comes along and it says, “Hey, the Power BI template that, or this is a Power BI template that takes data from an Excel file stored in SharePoint.”
Ben: Okay. And this is when I updated it last March 2022. It says, “Enter the path to the Excel file stored in SharePoint into the Excel web URL parameter below,” which is there. The path can be found there, the copy path button in the Excel app. Okay. Copy and paste in the full path, but remove the ?web=1 from the end. So, I’m trying to tell you here exactly what to do. So let’s just go and do exactly that. So, if we go to here and we have a look at my Planner tasks, that’s not the right one because that’s just the MPUG one.
Ben: So we’ll close that one down. So I need one where we’ve updated this regularly. So this is the one which gets updated for me regularly. You can see that I’ve got a lot of tasks or not a lot of tasks in there, but a fair amount of tasks towards the top in there. Not that many. [inaudible 00:16:38]. So in terms of the plans, you can see I’ve actually got the MPUG presentation plan is in there. So this is my full blown Excel file that this Power Automate, or this flow that runs every night. That’s the full one. That’s for one which updates … That’s one which updates this task. Okay. So how do I get the URL for this? Unfortunately, it’s not quite as easy as copy and pasting that URL in there. What we have to do is we have to edit the Excel file in the full blown version of Excel. So let’s just do that. That will open on a screen somewhere. Oh, sorry. Opening the desktop app. So, it’s going to open on another screen. Let’s just let that do that. Here it is.
Ben: So we need to find the URL of this application. If I click on file and then info, then I’ve got the ability to copy the path and that’s what you want to do. Let me just put that in Notepad for you, just so you can see that. Okay. And you can see, so the path is HTTPS, and then my SharePoint sites, and then the SharePoint and name of my SharePoint name that I created. And then effectively because I didn’t put it in any other document folder, it’s always going to be shared documents. And then the name of the Excel file. And then here’s the ?web=1 at the end, which we can get rid of. Okay. So that’s the path that I want and that’s the path that I need to put into Power BI. So I’ve just copied that. We can close down the Excel file now. We’re not interested in that. We can go back over to Power BI and we can paste in that URL. Okay. So, that should be good. And then all we need to do is click load.
Ben: Okay. So, assuming I’m logged in as the right user, which I am, then we’re going to be able to connect to that. You might get some authentication questions, please log in, please connect to the SharePoint site. But this is my default site and default tenants. So you can see that that’s just loaded up nicely. So that’s going to go and find these six queries, load them up. So these queries literally, we’ll go through them briefly, but they are literally the queries that allow us to view plans, labels, checklists, tables, and ultimately tasks. I know we’ll go bring the data in of course and visualize it for you. Okay. So let’s just go and have a look at a couple of things. We’ll open up Power Query. Again, you can do this at your leisure. But one of the issues I had was how do I deal with tasks that don’t have an assignment?
Ben: Because of course, if something didn’t have data by default, you don’t really show it in Power BI. And so we’ve got quite a few staging tables that I use in here, where if we have a look at this one, we’ve got a user assignment ID called no assignment, okay. Which we then end up merging with the user table, and those sorts of things to get the actual data that we do bring into Power BI. So if you are starting to store, if you said, “Hey, Ben storing this in Excel, it’s not robust enough to me. I’d want to store this in SQL, et cetera,” then you’ll need to go into each query and change the source table or the source data. So that it’s looking at the data, not from Excel, but rather from a SQL server.
Ben: And you could do the same with Microsoft Lists or wherever you decided to store this data in. But I do do quite a bit of playing around with the data. As you can see, the buckets are pretty easy. That’s just very simple at the end, we just append right at the bottom here. You can see, we can append something called another bucket. So I did end up with some. I’m not quite sure how I did it, but a lot of playing around, I did end up with some tasks, which didn’t have a bucket. I have also ended up with kind of blank tasks, but that’s blank plans, but haven’t done that for a while. For users, again, I ended up creating a user called no assignment or a user name called unassigned. That got me around the issue of not having … of being able to display and see tasks that didn’t have assignments.
Ben: We have the same sort of thing with checklists, and then obviously the big one is the tasks table, where again, we just bring in all of that data from the tasks. I haven’t cleaned it up. It is just as it is because it works. And then we have the labels as well. Okay. Because the labels have a name next to them and ID. So, we can bring that in for the one of the more complex things or not one of the more complex things of course but one of the things we needed to do was create some relationships. So those of you who are database SQL guys will know that this isn’t a particularly optimized view of what we’re doing, but it works.
Ben: Why is it not optimized? Well, we have bidirectional querying or filtering in this model, which isn’t always so great. And here effectively, we have a one to many, than a many to one. Again, bidirectional, which isn’t again, so great. But we’ve had to do it to get the data to work for us in a way that Microsoft give it to us. Okay. So that’s for the model view, you can, of course, go and query all of the data as you want.
Ben: You can see the assignments table here. I had to get the list of all of the tasks and the users that they’re assigned to. And then append for ones which didn’t have an assignment with this user that we called no assignment. Okay. What that allows me to do, ultimately, is to come in here and say, “Hey, just show me everything that is unassigned.” Okay. They may be tasks that are unassigned, because you’ve forgotten to assign them to somebody. Nobody’s going to do them if you don’t assign them to somebody. Yeah. Or they’re just generic team tasks that you might want to just plan in your plan before you make the assignment.
Ben: So it gives you the ability to at least track those. Okay. Once you’ve … let me just change this and sign in with my tenant account. Once you’ve modified or changed this then of course, that’s interesting. That’s great. Then we can go and publish this in the usual sort of way and publish it up to Power BI. Let’s just save this as MPUG demo. And for argument’s sake, we’ll just put that in my workspace.
Ben: And then if we go over to my workspace in Power BI, that is Power BI. Let’s go to my workspace. We should now have a new … did that not publish? Oh, love it when a demo goes horrendously wrong. Let’s publish that again. Let me check that. It’s good. Publish my workspace. It tells me it’s published. Maybe we just didn’t refresh it. I’ll give it enough time. So, okay. Logged in as the right person. Refresh this. Okay. There we go. So here’s the report, thankfully. MPUG demo. And of course we would go in and we can interact with that report. There, of course, is my MPUG presentation plan, et cetera, et cetera. So, just to wrap this little bit off on the Power BI side, what would you do with that now? Well, of course you would make sure that your MPUG demo is set for a scheduled refresh. So my Power BI, sorry, my flow runs every night at, if you remember, I think it was 11:00 and therefore I might set this up to refresh daily at let’s just say 1:00 AM.
Ben: It’s just displaying that on a different page. Nice. And then that would work. So that will just make sure that that report is refreshed from my Excel file. And my Excel file is updated every day because the flow that’s updating that Excel file runs at pretty much midnight or quarter past midnight every night. Okay. So, that’s how that works. Okay. So far so good. Do we have … has anybody got any questions they want to ask? Is this making sense? I’m going to take pause for 30 seconds whilst you just type something into the questions and feel free to ask away.
Melanie: I haven’t seen questions yet, Ben, nor any references to Pink Floyd, so …
Ben: Oh. Congratulations. But then, you and I, don’t take this wrong way, of an older generation. Maybe. I wasn’t sure if my video was on. Is that on or not?
Melanie: You are up on my side here.
Ben: Fantastic. That’s great. So here’s for a cup of tea. Great.
Melanie: Oh, okay. So we have a question.
Ben: Okay. Now that’s not … so, I can see the asker. I won’t say. “Hello. Nice to see you again. What is the value of this solution,” asks my good friend, Eric. Well, Eric, I’m working with a customer now who uses Project Online, Project for the web and Planner. And they want, they’ve got 40,000 employees, but I’m just working in a small portion of that. They want to be able to see all of their tasks and plans in one place. They want to see what’s outstanding. That’s the value because once you’ve got anything more than three or four plans, then being able to report on outstanding tasks across three or four plans is difficult because you have to go into each plan individually, work out what’s left for which users. So which plans are or which tasks are late. So I have to do that in my input presentation.
Ben: I have to go into my Downton Delivery or Downton Delivery and do the same in there. Have a look at which ones are late in there. I have to do the same in RLS and I have to do them in all of the plans that I have. So the value, Eric, is that I can now just do this in of course Power BI. Okay. In one place. The value for my client that I’m working with aren’t exactly this [inaudible 00:30:28] but we can have a look at all outstanding plans across all of our projects and our Planner tasks as well using Power BI. We can bring this all into one place. Okay.
Melanie: So Eric has followed up saying, “Good added value. How would you deploy the solution? What is required?”
Ben: Well, Eric, you are taking the next … we have the next 30 minutes to do that. So that’s exactly what we’re going to do. So to deploy this solution, I put all of the bits that you’d need for this on GitHub here. Okay. So just let’s take you through. The licenses is [inaudible 00:31:13]. The flow map is for those people who cannot for some reason deploy this. And I’ve seen this in a few instances where people cannot actually deploy the solution that we’re going to do. And so the flow map is a visual representation of everything that I’ve mapped and done. Okay.
Ben: So you can print that off on [A3 00:31:37]. That’s how I produced it. And it should give you … I can’t zoom in anymore on this, but it will show you exactly what you need to do for each table in the Excel file. Okay. So happy with the flow map. Let’s just zoom that back to normal size. We’ve got the Planner Report.pbit, which you’ve just seen me download and connect up. We’ve also got the Excel file called PlannerTasks.xlsx. And we went through this before and we know that we need to store that file somewhere, upload it somewhere into a SharePoint site. So, again, it’s a [inaudible 00:32:23] and creating a new site. Once you’ve created a new site, give it a name and then let me just close that down. Give it a name. And then within the sites you need to upload and store that Excel file somewhere. Okay.
Ben: So that’s that. The bit which will hopefully save you all of the work is the exported flow. So what you can do if you’ve got a flow that is useful and you want to share it with people, then you can click on the export button here and download that as a zip file, which is exactly what I did. What you’re then able to do is go into flows and then click on the imports. Let’s go through that process because a few people have had problems with that issue or that process. So, let’s just go and do that. So, here’s the zip file. You can see it appends it with a date. So we’ll just download that. Okay. And we see we’ve done this a couple of other times. So again, let’s just take that date modified. We’ll just rename that. Okay. You can call it whatever you want, ultimately. So in flow, then, I can import a flow. So once I’ve downloaded that from GitHub, we can go and import a flow.
Ben: Just whilst that’s importing, we’ve got another question. Does each Planner user need to create [inaudible 00:34:14] install the flow? Does each Planner user? No. Just the person, just kind of what I would call your low code stroke no code administrator. The person that normally runs the flows in your organization. Yep. Or maybe not the person who normally runs the flows, but the person who wants this to run for them. The person who’s responsible perhaps for doing the maintenance or the management of those tasks to see which tasks are being performed, et cetera. So you really just want one person running this across. You can run it across multiple plans.
Ben: Obviously, the person that does install it has to have read access to those plans. So you’ve got to make them part of the team that those plans are associated with. Okay. So that’s hopefully that question done as well. So, back to the import. It says, “Let’s import a package. Import flow created outside of a solution into this environment. Flows created in a solution can be imported under the solution.” So I’m not too concerned about the solutions. We’re just going to choose the package to load up. So let’s just go to my downloads again. There’s the Excel file that we’ve just … sorry. The Excel file. The zip file that we downloaded from GitHub. I’ll open that. Down at the bottom, it says, “Uploading your package. Don’t navigate away.”
Ben: So again, another question from Eric. Are all Planner tasks appended to the same Excel file? Yes. Remember at the beginning I said that we removed all of the existing tasks from the Excel file and then added them all back in. Okay. Why do I do that? Well, there’s no way of me knowing if a task has been modified. You might have changed the start date. You might have changed the end date. You might have changed some assignments. Okay. And there’s no way of me saying actually, “Have you deleted a task?” So if I’ve deleted a task in Planner, how do I know that I’ve deleted that if I just kind of amended the Excel file? So the easiest, most pragmatic way, the simplest way is to delete everything from Excel when we start and then go and write all of the new tasks in. So if you only change one task, yes. It’s a waste of somebody’s processing power. But as I say, you pay Microsoft to run your flows. You don’t pay by processing power. Okay. So it becomes an issue for those guys.
Ben: Okay. So, and just a quick question from Charles here. Are the projects from Project Online or Project from the Web? Charles, in my environment, I use both because Power BI can read from both. So it’s absolutely fine to use either one or the other. And you’re right, Eric. There will be only one Excel file for all Planner users. Yeah. If you want to split it down in there, then begin to use role level security or amend the flow just to read in certain files or have one major file in Power BI and then one data set and then publish different Power BIs with different filters for different users in your organization. That’s absolutely possible as well.
Ben: Okay. So what do we need to do now? We need to go and basically get this imported. Now, I’m importing it to the same environment that I exported it from. So you might see something slightly different on this. I’m going to say, “Let’s just create this one as a new one.” And I’m just going to prefix that with MPUG. We’ll save on that. And then I have to go and choose some accounts to set up. Okay. And again, it’s remembered that we have this account called Ben MVP, so let’s just go and select all of these. What we’re doing is getting rid of these red exclamation marks and replacing them with crosses to say, “We’re good.” Why it’s a cross, I don’t know. I would have stuck a tick there, but there we go.
Ben: So we’ll go through and select. If you don’t have an account in here, then you click on create new. I’ve had people ask me questions via YouTube. What do I do here? I haven’t had an account. Well, have a look. Create new And if you haven’t got a create new, okay. Or well, there’s always going to be a create new because that’s the button which will be there. So we just go and select the relevant accounts. Make sure they’re all done. Make sure all of those red exclamation marks have gone from here. We can also go into, I think it’s connectors and again, set up new connectors. Once we’ve done that, then this is all looking good. Okay. We can click import. Okay. So that will go and import their package and that will import the flow. It shouldn’t take much more than 30 seconds to a minute. So ask any questions you want now. Okay.
Ben: Okay. So if that imports successfully, you’re going to get some green check boxes next to everything. If it hasn’t, you’re probably going to get some errors up here, which again, I do cover off in a YouTube video and we show you how you can get around those errors. But often there’s a button here which says, “Save flow.” And then we can go in and we can modify that flow. Okay. So it will allow you to save the flow, but then you need to go in and modify it and you’ll need to go in and modify it anyway. And the reason is that if I go into my flows now we’ve got a flow called MPUG, add Planner files to Excel, online business. You can see that that is … it needs to be turned on. So at the moment it’s turned off. So if we turn it on, then we can go and edit that flow.
Ben: So why will you need to edit it? Well, you’ll need to edit it because it will have effectively my settings in there. If we go to this first action here, list rows in the tasks table, then it’s already predefined for location and the document library and the file and the table. Okay. So this is called PlannerTasks.xls. If you remember in here, the file is also called PlannerTasks.xls, but where I’ve stored that and where you’ve stored it will not be in this location. Okay.
Ben: So you need to go in and modify your flow to select the right location. So if I wanted the MPUG presentation that I just created, there’s the MPUG presentation. We would then need to go and it will go and query for document library and it might resolve it or it might not. So I’m going to clear the custom value and then I’m going to select documents from there. Then I’m going to select the file and the tasks. And then I’m going to go and select the table. Now, it’s remembered tasks table. I don’t know if that’s because the ID is the same, but I’ll just go and choose task table.
Ben: So you need to do that for every interaction that you have with Excel. So, this interaction down here, we’re going to need to go in and add rows into the table. So we’re going to need to modify the same thing here. So let’s just go and do that for MPUG.
Ben: [inaudible 00:42:50] document library, select the documents again. Planner tasks is okay. Again, sometimes you will see this. This is just a timing issue. Okay? It’s not gone and found with data yet. So if I go and clear out Planner tasks and select Planner tasks, and then we go find the task table, and once it’s found the task table, these values typically are saved. So you’re not going to have to go and input these values again. They’re all there for you. So I just tend to expand everything to make sure that we’ve got the right thing working. So again, we’ll go and pick up the MPUG piece from here. I’ll just do this one on the first one. So you can see it’s working. MPUG presentation, document library, documents, Planner tasks, task table.
Ben: You have to go and reselect the Excel file even though it’s the same name. You can of course call your file a different name if you want. So go and delete those. That will be that branch done. I have to do exactly the same for the other branches. I’m not going to do that now. Obviously, if you want to change when this runs, we can come up here. We can click in settings and oh, not in settings. We can edit, okay. This runs at 23 every day. So we could go and just change how often that runs. This is a project management tool, right? So I can’t see that you’re going to want to … it’s not transactional system. It’s not like credit cards or sales or toll booths or the amount of streaming data I’m using whilst I’m watching Netflix or something like that.
Ben: It’s not real time. So it’s just kind of transactional. So I can’t honestly see why you’d want to run this more than once a day, maybe twice a day. But again, this might take an hour or so to run. So, you’ve got to be a bit smart with that. If you are wanting to run it longer, sorry, more often than, you’re going to have to use probably Microsoft Lists or something like that. But again, just be aware of what can happen here. You might be refreshing your data or you might be deleting data, and you’re trying to Power BI refresh at the same time. One of the things that I would do to amend this is once all of these have finished, then I would add an action that just goes and performs a power … kicks off a Power BI refresh. So that would be a fine thing to do as well.
Ben: Okay. So that’s all well and good. I can just use the flow checker here to make sure I’ve got no errors or any warnings. And then I can save this file. And once we’ve saved it, you can see up here, it says, “Saving.” Then we can come back and we can open up and test this. Okay. So I am going to test this. So I’m going to right click in here. And then in my MPUG Planner tasks, we’ll open that up and we’ll just put that over on that side. Let’s go and have a look at tasks here. So first thing we should see is we test this, is for all of these tasks should start to be deleted. Okay.
Ben: So, I can’t remember if it deletes from the top or the bottom, but we should see if the row number is changing. Okay. So back into flow here. Test. Let’s just test that manually. Test. Too many user interface buttons here. I have to click run now on this as well. Okay. So go to our run flows page. This should be running. And remember we went in and modified the tasks piece here. So I’ve currently got 314 rows. Let’s just have a look at … oh, I’ve got this running twice. Okay. That’s interesting. Let’s stop that one.
Ben: Okay. Cancel that one. Go back here. I’m going to stop this one as well because I don’t like it when I’ve got multiple things going on that I’m not sure what’s happening about. Let’s cancel that. Delete that. Okay. And then let’s go on resubmit. That’s fine. Let’s just resubmit that. Okay. We can see already we’re losing tasks from the Excel sheet here. So I like to leave when I’m starting to understand what’s happening, I like to leave this flow running because it will tell me what it’s doing and how long it’s taking in each portion of the flow. And again, you can see it’s just going to go through and it’s in this area here where it deletes each row and then it will go on at the back end. So that’s the flow running. So that’s how we would go and get the flow.
Ben: Remember we go into GitHub, we go and download effectively all of this data anyway, because you’re going to need the .xls file. You need the .zip file and you need the Power BI template file. It’s worth subscribing to this and watching this so that when I do an update, you can come in here and work out, do I want to implement that update or not? Okay. So we’re going to let that just carry on running. We’ve got a few minutes. So we’ll just go through any questions that we’ve got here. So we’ve got … so I’m working with an external vendor who is managing their tasks in Jira. [inaudible 00:49:02]. So do I get them to create an export from Jira and then bring it into Excel, merge it with my project Planner tasks, build a workflow to update and run the Power BI report?
Ben: Well, I had a customer who was running and working with Jira as well. And I just, you can query Jira directly from Power BI. Okay. So that’s what I did. You need to write some Power Query to go and do the query, but that’s exactly what I did. I brought tasks into Power BI directly from Jira. So I don’t worry about flow or anything. You can connect up if you know how to Power BI directly into Jira that isn’t the Jira connection. A nice Jira connection rapper for that, if you like, but if you can use Google, today who can’t, then you can enable Power BI to connect directly to Jira.
Ben: So that’s that question. For large deployments are there performance issues, bracket slowness? Yeah, absolutely. Okay. You could do this in multiple ways. So you can see that this is now kind of stuck here and it’ll be stuck here for a little while whilst this runs. It’s been running for three minutes. It seems to have bursts of energy. You get some CPU time, if you like, and then it backs off and gives the CPU to another flow running for somebody else and then it’d come back. So yeah, there are performance issues. So, couple of things that we can do for that. One is, as I said, don’t use Excel. Use instead, use this little tool called Microsoft Lists. So somebody told me that we saw a 10 times improvement in performance with Lists. So I’ve never really used them as you can kind of see, but one thing you are able to do, because I need to think about, I thought about this for this channel and the YouTube channel and everything else is, do I keep this in Excel or do I put it in Lists?
Ben: Well, if I keep it in Excel, it’s just that easy. People can download the Excel file, put that up to SharePoint. And it will just run. If I was to do this in Lists, then I’d have to teach people how to set up every list. Okay. Which is potentially difficult. But one thing you can do with Lists and I’ve not tried this, is that you can … yeah. You can create a list from an Excel file. So we could go and find our Excel file. This is going to the file system. So I won’t find that. But then within the Excel file, I think you can go and say, “Hey, let’s go and create a list based upon this table.” And I’ve got these tables in there. I have my tasks table, users table, assignments table, et cetera, et cetera. So, that should be relatively easy for anybody to do who’s got a level of confidence in IT.
Ben: Of course, what you then need to do is edit your flow so that it’s not writing to Excel, but it’s writing to the list. And of course you then need to edit your Power BI template file so that it’s not querying data from Excel, but rather it’s querying it from List. Okay. But you know, in terms of Excel, it will quite happily store a million rows in a table, we know. I don’t know how long it would take to write to that from flow, but that would be how I would … that’s one way I’d solve that. The other way I’d solve it is if we look here, these are running effectively concurrently aren’t they? So there is a theory that actually I could just list for rows in the tasks table. And then once I’ve done that, I could probably list for rows in the buckets table.
Ben: And then once I’ve done that I could list for rows in the plan table, et cetera, because ultimately this function here, list for teams, is doing … that’s on the run. Let’s go and edit that. This function here, listing the teams, is doing the same thing across all of the parallel components of the flow. So I could combine this and just have … let’s just list for teams once and then list for plans for the group. And then this one lists tasks and that’s okay. And then apply to each task. I could probably branch here as well.
Ben: So you might want to test this. If I was deploying this for thousands of users, then I would say there’d be some work to do to test this. Okay. So hopefully that answers that question. Let’s just see if there’s any other questions that are going to come in. Whilst we do that, we’ll just have a look and yeah, we’re still deleting tasks out of our Excel file here, as you can see. Okay. Have we got any further questions before I begin to wrap this up?
Melanie: I see no more questions, Ben.
Ben: Okay. Thanks, Melanie, on that one. Let’s just briefly then talk about what we did and just review everything. So, very quickly, we took data out of Planner. We put data into Planner, of course. We had to have multiple plans. Those Planner plans have to be associated with a team in Microsoft Team that you ultimately have to be able, that you are a member of because you’ve got to be able to read those plans. We read those plans in Power Automate. That’s really where all of the crux of the work and the configuration is going to be done here.
Ben: Remember, you’re going to have to come in and modify that Power Automate to look at your own set of teams, but also to say, “Hey, where do I want to store the data? Where’s the location of the Excel file?” So Excel’s fine. As long as this is not taking, I would say more than five or six hours to run, I’d just leave it in Excel because why would you care if you’re running this at midnight? So long as it’s there for you when work starts at 8:00 in the morning. It’s different in multinational companies that work across different time zones of course.
Ben: Alternatively, you could have stored this in a Microsoft List. Okay. That is of course free of charge. Or if you wanted to have a premium connector, you could store that in a real database, perhaps something like SQL, but there is a cost to that premium connector cost for flow. And then of course we bring and consume the data into that Power BI template, which is going to ask you for this location. That Power BI template, again, if you modified any of these, you’d have to do the associated modification here in the Power BI template file. And you might do that for performance reasons would be one reason for doing that. Okay. Now, for further information, obviously there’s these two demos that we’ve done.
Ben: There’s also, if we just open up good old YouTube file or youtube.com, you can catch me. Everything I’ve covered is on this session anyway, is there on YouTube, but sorry, it is there on the MPUG. You won’t learn anything else on YouTube, but you can either feel free to message me via YouTube and reply to one of these recordings or message me on the MPUG that on the MPUG forum, because I will get those comments as well. If you put a comment again, to this recording, I will see that. Okay. That’s about it from me. Remember, if you do want to reach out, you can get me at any of those contact details there. Yeah. That’s about it. Melanie, back to you, I think to wrap up and give the codes, et cetera, for this session.
Melanie: Yes, Ben. Thank you. And I have been spamming your contact information all over, so …
Ben: Thank you.
Melanie: You’re welcome. You’re welcome. Thank you for a great session. It was super informative. Members here today, thank you for choosing MPUG to grow your skills with today. This session along with your other PDU information will be automatically added to your transcript. The PDU for today is done by Activity ID. It’s on the screen now. And I’ll share a link later today with the recording, the PDU and a quick survey. So, please share your thoughts and interest. I’m sure Ben would love to hear your thinking about this session. And just a quick reminder, there’s some great sessions coming up as well. We have Mike Thomas, who’s a super Excel presenter. He’s going to give you quick tips to be more productive. And then we start a three part series, an overview of the PMBOK Guide 7th Edition. So we’re going to get into that deeply. So, again, thank you for joining us today.