Quick Links

Webinar Recap: Power BI for Business Users and Project Managers – Part 3: Creating Reports using the Power BI Desktop App

Please find below a transcription of the audio portion of Ben Howard’s session, Power BI for Business Users and Project Managers – Part 3: Creating Reports using the Power BI Desktop App, 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 live recording of this webinar at your convenience.

Kyle: Hello, everyone, and welcome to Part 3 of the Power BI for Business Users and Project Managers course. This third and final lesson will cover creating reports using the Power BI Desktop app. My name is Kyle and I’ll be the moderator today. And today’s session is eligible for one PMI PDU in the strategic category. The MPUG activity code for claiming that with PMI is on the screen now. Like all MPUG webinars, a recording of this session will be posted to mpug.com shortly after the live presentation wraps up and all MPUG members can watch the recordings at any time and still be eligible to earn the PDU credit.

Kyle: All of the sessions you ought to on demand could be submitted to your webinar history, and the live sessions you attend are automatically submitted. Within your history, you can print or download your transcript and certificates of completion, including the one for today. You can access that by logging into mpug.com, click on my account and then click on the webinar reports link.

Kyle: If you have any questions during today’s presentation, please send those over at any time using the chat question box on the GoToWebinar control panel, and we do plan to answer those questions for you during the session today. All right. We’ll go ahead and get started. So we’re very happy to welcome back Ben Howard today. And at this time, Ben, I’ll go ahead and hand it over to you to get us started.

Ben Howard: Hi, thank you very much, Kyle. Welcome to everybody. Let me just see if we can get a monitor on here. That one will probably do it. Kyle, are you able to see a presentation now?

Kyle: Yeah. It looks good.

Ben Howard: Brilliant. That’s fantastic. Thank you. So welcome, everybody. Welcome back to Power BI for Business Users and Project Managers Part 3. Can’t believe we’re already here. This part is all about creating reports using the Power BI Desktop app, and then of course, publishing and sharing both reports within our organization and potentially beyond that. Now there is only so much, of course, that we’re able to cover off in an hour. So let’s just have a look at what we are going to do.

Ben Howard: Firstly, in fact, I won’t repeat this. We did it last time. I suspect you’re going to be a project manager who needs to report on the status of one or more projects. And that’s really what we’ve designed this series of sessions for, or rather who we’ve design them for. Just a quick recap. My name is Ben Howard and I’ve been using Microsoft Project and involved in Microsoft Project since about 2000. So that’s 21 years now. I’m a author, blogger, trainer, consultant, all of those general things.

Ben Howard: The interesting piece that I’d like to pull out for you now is my YouTube channel. So I’m going to put that YouTube channel in the chat. So you should be able to see. Oh, that’s the wrong one. Let me just grab that. Oh no, it should be there. I think it has come in with the… It came in as I looked at it with the formatting. So it should be in the chat. If you can’t see that in the chat, then, again, just post something in the chat to let me know. So go and have a look at that YouTube channel.

Ben Howard: On there I do post fairly regularly on Power BI and Project and those sorts of things. So over the last two weeks or three weeks, we’ve done an introduction to Power BI Suite. Last week we then did a deep dive into shaping and using this tool called Power Query. And today, we’re going to take what we’ve learned and go and create reports using the Power BI Desktop app. So what are we going to look at and what’s the structure really for the next hour or so? Well, once we’ve got the data into required shape, which is really what we did last week, then we can begin to visualize it.

Ben Howard: And by visualizing, what I mean is we can go and build reports and then dashboards from those reports for our users in our organization and our teams to go and view. And mentioning teams, of course, you can host Power BI reports in a Teams site. So Teams has a couple of connotations these days, but Power BI is very much built to play nicely with the other applications. And we have a collaboration applications that Microsoft have, Teams, of course, being one of our main ones.

Ben Howard: What we’ll do specifically today is we’ll go and take the Planner files, which we kind of looked at last week, and we’ll bring those Planner files into Power BI using Power Query and publish those reports and create some dashboards around those Planner files, again, so that they can be shared within the organization. We’ll also have a quick look at those reports and dashboards on a mobile device. And just kind of give you a view of what we can do on mobile devices. So that’s what we’re going to do.

Ben Howard: Where are we in the process? Well, ultimately, we should be familiar with Power BI Desktop or that’s what we’re going to be using today. We’ve looked at how we can get data from either within sort of Excel files, or OneDrive, or SharePoint, or we could go and get data, of course, from Project Online, which we had a look at. We might have data that’s coming out of an API via Jira, et cetera. And we can bring that data into Power BI. We might, of course, combine that with some third party data that we get from the web.

Ben Howard: Once it’s in Power BI and we’ve cleansed it and shaped it using Power Query, then, of course, we can go and build our reports and our visualizations. And that’s the stage that we’re at today. Once we’ve done that, then we have to publish that report so that it can be consumed by other applications. And what I mean other applications, basically consumed by other users using a browser or a mobile device. So this whole process of creating this report and then publishing everything is what we’re going to cover off in the next 50 minutes or so.

Ben Howard: So that’s it for the slide where today, which is brilliant. Let’s just go and have a quick look at what we’re going to do. So this is Power BI service. So I’m logged into app.powerbi.com. And I’m actually looking at a workspace called MPUG. You can go and create new workspaces. So if you’ve not played with a Power BI service, then before you publish anything, really you want to go and create a workspace. That’s a question of just clicking on workspaces and selecting, create workspace here, and you’ll be prompted to enter workspace names, et cetera.

Ben Howard: You can have as many workspaces really as you want. If you haven’t got any workspaces, then you’ll default to something called my workspace, which is your own personal workspace. You can’t share reports from my workspace. And so you’re better off going to create your own workspace for testing and for training, et cetera, which you can then share with people in your organization. Now to do that, notice that you will need a Power BI license. You’ll need a Power BI Pro license, at least.

Ben Howard: I’m looking at something here called Premium Per User, because I just clicked on that three days ago just to see what that gives me, but ultimately, it gives me a little bit of premium capacity. So let’s go back to the MPUG workspace. We’ll just type in MPUG in here and go and select that workspace, because that’s the workspace I’ve already been working with. So what are we going to do? Well, I’m going to bring in data from a Planner file rather than creating all of this from scratch. Just as a recap, we should have looked at this last week on GitHub.

Ben Howard: If you search Ben-Howard, then you’ll find that there’s a repository for a Planner Power BI template file. What that template file does, is it imports exported Planner files. What do you mean is an exported Planner file? Well, if you go over to Planner, so here’s a Planner, a plan called Bobins. It’s not got many tasks on there, but it’s got a few tasks some of which have been completed, some of which you’ve got different labels set with those tasks, we’ve got different buckets that we can see here.

Ben Howard: We also have resources on some of their tasks, Ben Howard, Andrea, et cetera. So what you can do is you can click on the ellipses here and you can export the plan to Excel. And basically, it exports that plan with a name. The plan name, of course, is the name of the Excel file, is named as per the plans. So in this case, it’s Bobins. But what the Planner template file does when you open that up in Power BI is it will ask you for the name of the URL, if you like, of the folder where you’ve stored all of those Planner files.

Ben Howard: So at the moment, mine are all stored in this file or in this folder called MPUG 2021-Power BI Part 2 Planner files. So if I browse to there, you would see a multitude of Excel files. And that’s really where I wanted to get to. So we can see that in the data model here or in the model view in Power BI, I’ve imported all of those files, and each file effectively gives me the… Just have a look, we’ve got the Planner name. So if we drop this down, you can see that I’ve got two, four, six Planner Excel files in here.

Ben Howard: This is the name of a Planner name. Within each plan, of course, I’ve got multiple tasks. Here’s a list of all of the tasks that I’ve got in those Planner files. Each task, of course, is associated with a bucket. So here’s the bucket name. Now, here’s where you want to be a bit consistent. I was testing this across multiple languages. And so some of my bucket names are in French, and that’s not going to be very smart if I’m trying to consolidate things. Notice I’ve got some other buckets called on hold, or on hold or removed.

Ben Howard: Now I’ve got one called pending edits, and I’ve got one called pending review, and I’ve got one called planning start. So really when you think about this and you design your Planner environment, in my case, you would want to do some consolidation, some standardization of the bucket names. There’s no point in having a bucket called complete and completed, or two separate buckets across two different plans. It doesn’t make much sense. So just be aware of that. Progress is one that Microsoft creates, so completed, in progress, not started.

Ben Howard: We have who the activity is assigned to. Now notice, an activity might be assigned to multiple people, of course. And what I’ve done is I’ve split these out here. So that was something that Power Query took or takes care of. It’s created by created date and a few other things that we’ve got, including the different labels that we could have associated with an individual activity. Now, one of the other fields that I’ve created is a field called new status. So you can see my values in new status are completed, in progress, late, or not started.

Ben Howard: So that begins to complement or indeed extend Microsoft’s typical progress field here. So that’s a quick review of some of the data that we’ve got. We can see if we have a look at the bottom, I’ve got 381 rows of data. So that’s how many tasks that really we’re going to visualize. Just to expand that. So it’s always worth having a look at the data that you’ve got with the data tab, but ultimately, this is our starting point. Now, I’ve opened up this Power BI template file which I showed you from GitHub, and ultimately, it’s already got some visualizations on there.

Ben Howard: So, for example, this one here called plan by status and priority. So you’re never necessarily going to start from scratch, but I am for today’s session. So what might we do on here? Well, let me show you, and let me show you how I always think about starting to create Power BI files. It’s a bit like starting to create PowerPoint files. We need to decide on some standards. For this standard, I’m going to work in this, obviously, this visual here, this pane. You can see the canvas here.

Ben Howard: If I click on the canvas, then I’ve got the ability to do some formatting just on the canvas. So I’ve got some details about the page information. Notice I’ve already renamed it to be summary, et cetera, et cetera. There are hundreds of options in here. So plainly, we don’t have time to go through them all. I’m going to just build report and pull out the salient options that I think are going to be useful for you guys, the ones that I use most often, and we’ll freestyle this, if you like.

Ben Howard: We’re going to build a report. It won’t be the prettiest report, but I’ll pick out some common factors and ideas that I put almost into every report. I’m going to try and build three pages if we have time. I’m going to build a summary page, a drill through page, and a decomposition page as well. They’re all pages which are represented in that Power BI template anyway. And then typically, certainly, the summary and drill through are things that I will often do to provide additional information for people.

Ben Howard: Once we’ve built this, then we’re going to publish it out of the service and we’re going to review it on the service. And then once we’ve reviewed it on the service, I’m going to show you my mobile phone. And we’re going to review the same report on my mobile phone as well. So let’s get started. In the summary page here, I’m going to put a background. Well, first I’m going to put a page alignment. The page alignment, I always set to be in the middle. It just gives a little bit of real estate at the top and a bit of real estate at the bottom.

Ben Howard: It’s just visually nicer for me. I’m also going to put a page background on here. I don’t always do this, but more often than not, I will do. And I’ll choose a color which is to do with my customer or the whole look and feel. Now, this has to do with Planner. So I happen to know the Planner color is a red blue green of 21 and then 122. Oh, let me type in there correctly, and then 66. So that gives me that nice Planner green. So that’s okay. Now, the transparency at the moment is 100%. I need to change this to 0%. And then all of a sudden, I’ve got a nice green background.

Ben Howard: You may hate this. You may want to add an image in, which a lot of people will do. I don’t mind. But I’m just going to leave it there. There’s the green background for me. I’m not going to do anything else on that. Now, every page should have a title. So I’m going to come up here and I’m going to click on insert and I’m going to choose text box. So there’s the text box. You can see it here. So this is a visualization which has been put onto the screen. So we are able, because this is a text box, just to enter some text in there.

Ben Howard: And I’m going to say Planner tasks overview. Now that’s great. It comes in a Segoe UI Light, which is fine. And I highlight all of that. And I’m going to change that away from 10.5 to 32. You need to have a play around with this and find the standard that works for you, and your organization, of course, might already have a standard. Many organizations will give me a PowerPoint template provided to them by marketing. That is an absolutely great place to start when you’re designing your Power BI report. So Planner tasks overview.

Ben Howard: I’m just going to drag this over to the top. My laptop’s going pretty slow on here. It’s a bit slow at the moment for moving this around. It’s not exactly moving particularly freely, but that’s probably because we’re recording this and that’s probably for GoToMeeting is doing that as well. Let me just drag this down a little bit. So that’s fine but it’s not particularly nice, right? So I can format this particular visual. So this visual is selected because I’ve been editing it, and in visualizations here, I can format the text box.

Ben Howard: So what would I do again for this? Well, I’m just going to turn the background on for this. The background, I’m going to choose white and I’m just going to set the transparency to be zero again. So now I’ve got a bit of some text, which is as black against the white background. That’s fine. I’ll always pretty much center that text. If I click away from there, in order to get this in the center or nicely aligned, what I’ll typically do is I’ll choose a number of pixels.

Ben Howard: In this case, I’m going to choose five pixels. And in this general title or general section here, again, with the text box highlighted, I can choose the exposition. So that’s V, the number pixels from the left-hand side here and the Y position, which is the number of pixels from the top side. It gives me the coordinates. Now, I can then choose both the width and the height. Now, if we look at this, just for canvas, if I click elsewhere, I can work out how wide the canvas is by looking at page size.

Ben Howard: Well, you can see that this is 1280 by 720. It’s a typical 60:9 relationship, aspect ratio rather. If you needed to change this, then of course you could, including putting your own custom in. And so we need to do a little bit of mental maths here. So I’m 1280 by 720. So if we go to here, then I can pick up the general position for this visualization. It’s five wide. So if I’m 1280, I really want this to be about 1270 wide. So that gives me five pixels at the beginning, followed by 1270, followed by five pixels at the end.

Ben Howard: You only really need to do this once or twice, because then you can realign everything. And I try and keep things in even numbers. So heights of 70, or every five. Again, it makes the mathematics pretty simple. But you do want to try and get this right early on. Let’s now go and add some other visuals onto this canvas. If I expand the fields here, then I’m given access to all of the visuals which are available. Sorry, all of the fields which are available, which we had a look before when we were in the data tab here.

Ben Howard: I’m just going to show you some things, right? Let’s just grab the description. In fact, let’s just grab the plan name. I’m going to take my plan name. I’m just going to grab it and throw it onto the canvas here. You can see there’s my six plans. And because the plan name is text, then let’s put it in a visual quarter table. That’s okay. I could then take the task name and put it next to the plan name either here or here. Then now what I end up with is a list of each plan and then each task next to that, which is pretty neat, right? That’s fine.

Ben Howard: There’re different ways that I can see this data. I’m just going to get rid of plan name here. Just by clicking on a visual or by selecting a visual and then clicking on one of the other visuals, we can see that data in another type of way. So you might need to have a little bit of a play, but let’s have a look at a stacked column chart. That’s not giving us anything because it’s giving us on the axis here, which a stacked column chart has, it has an axis. We put the task name on the axis.

Ben Howard: Let’s go and put the task name in the values. Now, the task name is text, right? So it can’t display a textual value in here. What it’s done instead is it’s given me the count of task names. So you can see I’ve got my mouse over. We can see we’ve got 381 tasks. Now, if I go and put something else on the axis, such as the plan name, then that will display that data by plan. Let’s just go and change the formatting on here. Put a background on here. Let’s just turn that on and select that as white and have no transparency.

Ben Howard: Now, you can begin to see that a little bit better. So you can see those tasks are split up, in this case, by the project name. We could get a nice stacked column of that. You could see that by vertical histogram. We could see that by, let’s just take a pie chart, or donut chart, or a tree map. You see how these visualizations begin to work. Some are more useful than others for our particular piece of data. This one just tells me… I quite like this one actually, it just gives me the size, if you like, of each project in terms of number of tasks of a relative sides to each other.

Ben Howard: So you can see my Planner AV team is much bigger. So I’m just going to throw that there. Now notice as I move that around, on the left-hand side, I’ve got a little red line which guides me. It shows me how I’m going to link this in. So I know that that’s going to be five wide. And, again, we could go in and just click then on a general to work out the Y position, which I might want as 80. So that’s kind of visualizations. Now, there’s lots of things that we want in terms of numbers as well.

Ben Howard: So that gives us the kind of size of everything. I love this visualization. This is a card visualization. So just to utilize that, I’m just going to click here on anywhere on the canvas, click on card, and then I’m presented with the card. So what does a card do? Well, a card gives me a numerical value. So I’m just going to grab the task ID, doesn’t really matter which field I use, and drag it into that card. Again, let’s just change the formatting. So we turn the background on, and we’ll set the transparency to zero.

Ben Howard: So what this card is showing me is the first task ID. It’s pretty useless in this instance. I don’t want the first task ID, I want the count of the task IDs. That gives me 381. So 381 is the number of tasks that we’ve got in the plan. If you remember, we go back to the model view here. So the data view, there’s 381 rows. So I know I’ve got all of the data. Now, I’m just going to format this. I’m going to just give this, and I turn the category value off, which says count of task ID.

Ben Howard: I’m going to turn the title on, and I’m just going to call this, number of tasks. I’m going to put that centrally aligned, and I’m going to change the font color to be black. And, again, I’m going to have some sorts of standard on this. So I’m going to keep the Segoe UI, and I’m going to save the font sizes as 15. That looks pretty good. I’m just going to leave that maybe there. So that’s great. We’ve got the number of tasks. Now, one of the nice things you can do is you can copy these.

Ben Howard: So not only do I want the number of tasks, I want to have a look at how many remaining tasks I’ve got. So from a home tab, I can click copy. So this visual is highlighted. I can see that because of the grab handles. So I’ll copy it and I’ll paste it, and then we can just grab it and bring over here. Now, I’m not going to worry about this width between the two. I’d make sure that that was five so that everything looks nice. Now, instead of number of tasks, I want this to be the number of remaining tasks.

Ben Howard: So how do I do that? Well, let’s have a look at the filters here. You can apply filters to a visual, to the page, or all pages. So I’ll apply a filter to this visual. Now, if I think about it, how do I gather the number of remaining tasks? Well, let me just go back to my data set. And really for all of these things, you have to know the data. I created a field called new status, and everybody will have this if you’re working with Planner. And I’ve got, in that field, completed, in progress, late, or not started.

Ben Howard: Well, number of remaining tasks is probably going to be the ones which aren’t completed. So let’s just go and have a look at that. I can take the new status field, which is here in the fields list. And I can drag that onto this area here called filters on this visual, and I’ll add it in. And then I’ve got various filter types. See, at the moment I’ve got basic, but I could have advanced or top end. So we’ll choose basic. I’m going to select all the available fields and then de-select completed, right?

Ben Howard: So now I’ve got 107 tasks which are not completed. The title doesn’t make any sense now. So let me just go back to the title and I’m going to rename that to be remaining tasks. That’s great. And I’m going to do the same thing again once more. So I’ll copy and will paste this. And in this case, instead of remaining tasks, I’m just going to have the ones which are late. So I’ll change the status on here as well. So I’ll de-select all and just choose the late ones. Again, we’ll go change the title.

Ben Howard: So once you’ve got one visual set up, everything becomes pretty easier after that. We might just squeeze this in a little bit. Hopefully, I’m never going to have a massive amount of late tasks. So that’s for cards for us. I am going to come back to the cards soon. Let’s just have a look at the donut chart. So maybe we want to visualize the status of our fields using a donut chart. And I choose a donut chart because Microsoft have done this in their Planner visual anyway, in the Planner interface in their charts for this. So donut chart seems to be pretty cool at the moment.

Ben Howard: So let’s just go and have a look at our donut chart. Again, we’ve got some different values we can put in there. Let’s have a look, again, at the donut chart by status. So we’ll grab status and we’ll just drag that… I’ll just drag that one to the donut chart. That’s put that in the legend. We probably want that in the values. And notice how it’s put counts of status. Actually, I don’t want… Let me put that in the legend again. Now in the details. I always struggle with charts. New status in there.

Ben Howard: Let’s put the status in there. That’s looking a little bit better. Oh, I know why it’s like that. And then let’s, again, one of the bad things about putting a background on the actual page itself is then you have to go and put a background if you want to see these things easily on all the visuals. This has changed recently. So now I’ve got a donut chart, which gives me a view of how many are completed, how many are not started, how many are late. And if we expand that, then we can see how many are in progress as well. Might be useful to put a legend on here.

Ben Howard: So, again, we could click here and we could click on legend and do some formatting specifically on that chart. We’ll just make that a little bit bigger so that we can see the legend. Again, visually, always try and align things. That makes it visually appealing to us. So that said, a donut chart, again, I might go and change the name of that. Let’s just go and do one last thing. Well, a couple of last things, let’s go and put a bar chart on here. Let’s go and have a look at this by bucket name. So let’s put bucket name on the axis, and again, we’ll put the task ID in the values. Let’s go and change then the background.

Ben Howard: We’ll turn that on. Change that to zero. And, again, you can see the number of tasks by bucket, so how many are completed. And, again, you may decide that you don’t want the completed buckets to show because that’s not really giving you much value. And so, again, we could come into the filter for that visual, select everything, and perhaps get rid of complete and completed. And that will be fine. That’s great. One last thing. Let’s have a look at working out who’s doing some activities and which activities they’ve got remaining.

Ben Howard: So let me just drag that over here. That’ll be fine. Pop that up a little bit. Work out the five pieces later, the five mills, five pixels at the bottom. We’ll work that out later. In this visual, I’m going to work out who is assigned to which tasks. So I’m going to have the assignee name in here. Again, let’s just go and change the background. Turn that on. So we got assigned and then let’s have the task ID in the values. So we can see who’s assigned to which task.

Ben Howard: We might just make this a little bit bigger. Let’s maximize that. And in the legend, we can have a look at the… let’s just have a look at the status. So, again, we can see which tasks have been completed, in progress, late, or not started by resource. So this is pretty neat. Let me just have another quick drink. Now one of the nice things about Power BI is the interactivity between the visuals. So if I wanted to take this task called extension, I could click on extension and then you would see all of the other visuals change based upon what’s been selected. And things are either highlighted or not.

Ben Howard: So you can see for extension, if I mouse over here, then 46 effectively of those tasks have been completed. So what else has changed? Well, we’ve got a total of 56, sorry 53 tasks via extension. The remaining tasks are seven. So that’s seven remaining, of which four are late. And we could see that Andrea Howard has got some of these, then Howard’s got some, and we’ve got some which have been unassigned. Of course, we should be looking at the late ones here. So not too concerned about anything that’s completed. But you can begin to see the data. And that’s really neat.

Ben Howard: That’s a real value out of Power BI. But notice all of the activities or all of the visuals change. What happens if I don’t want a visual to change? Well, let me have some high level figures. So I want a card which is going to show me all of the tasks. So let me just copy this again. Let me copy and paste. Put this over here. We just throw that there. That’s nice. This visual, this card is going to show me all tasks. So firstly, I’m going to change the title to be all tasks. And for this demo, I’m going to make this specific. This is never filtered.

Ben Howard: So we’ll just apply no filter. All right. No filter in the backfield there. So how do I get this visual, this all tasks no filter visual to not have any other interactions apply to it? Well, what we have to do is we have to click on the visual itself. Then we can click on the format tab at the top. And you’ll see here, right on the left-hand side, there’s something called edit interactions. And I’m going to leave that mouse hovered over there because it says, change how visuals interact when data points are selected. While in edit mode, select the source visual and choose behaviors (of the other visuals).

Ben Howard: It doesn’t really have a bracket so I always put that in, choose the behavior of the other visual using the icons that appear. So how do we get into edit mode? Well, I click on edit interactions. And you’ll notice it’s slightly grayed. Now, when I select another visual, you can see for each of the other visuals, I get one or more little icons that appear. And these icons, if I hover over, either say non, or they say filter, or in this case they may say filter or highlight. So in the case of a bar chart or some of the other charts, we can either filter or highlight.

Ben Howard: So I’m just going to select non. So when I click on this visual, it’s going to have no impact here. When I click on this visual… Now, I need to do the same for every visual. So when I select this visual, I can say that it has no impact on this visual. So this can become a bit tedious, but it is the way that we have to work. And once I’ve done that, I can come out of my edit interactions page. Now, this is just bugging me a little bit. So let me just neaten that up. That will do just about.

Ben Howard: So what happens now is when I click on Planner AV team, all the other visuals change, except this one, because I said let’s not interact with that. And when you mouse over a visual, you’re able to see the interactions to that visual. So there’s this little filter button here and we can see what things are being applied to the visual in order to interact with them. That’s good. That’s good. That’s good. That’s good. Let me just save this. But once you’ve done some work and you be afraid to lose it, always click on save. Now, what about if I wanted to have a look at what Conor’s up to or what Ben Howard’s up to in some more detail?

Ben Howard: Well, this is where we use drill through. So drill through, I’m going to quickly do the same thing here. I’m just going to change the page alignment to be in the middle. I’m not going to worry about the page background here. I will put a summary on here though. So I’m just going to copy this and we’ll come into drill through, and I’m just going to call this task details. And this is just going to be… what I’ll do is I’ll just put a… I made a table in here. In that table, we will just have the plan name, the task name. We could pretty this up very much. We would have a start date. Let me pull that up there.

Ben Howard: The start date, as you can see, is expanded into year, quarter, month, and day. If that happens to you, just click on the start date and just say, “I want to see the start date, not the hierarchy.” So select the start date. Displays about a little easier for me. If I don’t like the date format, then again, highlight the field, come up to format and choose how you want to display the date. So I’ve got a long date at the moment. I’m just going to choose a short date. That’s going to reformat that field. So we’ve got the task name. Oh, we really want the plan name then the task name, don’t we? So we move that up.

Ben Howard: Start date and we could have the new status. And some of the other interesting data. You can grab that. Now, again in here, I’m just going to change the formatting a little bit. We’ll select the values. So I’m going to choose a text size to be something sensible. I think we’ll go for 15 for the values, and I always tend to keep the column headers the same as well. So 15 for the column headers. Again, I do some formatting on here. Now, here’s a point where we’re going to begin to use another card. So let’s just grab a card and put that in here. In the card, I want to display… Let me undo that because I have the wrong thing selected.

Ben Howard: Select nowhere. Choose the card. In the card I want to display the person that it’s assigned to this particular task. So I’m just going to put Andrea up there. Again, notice how the font is different. Always kind of winds me up this. So the data label is going to be from a font perspective, that’s for Andrea. This is Segoe. And I think we selected task details was probably 32. And that’s okay. That’s great. I’ll turn off. So, again, it’s data label. Just have the right one. We put Segoe UI Light. That’s looking a bit better. So these are the tasks.

Ben Howard: What I want to happen is when I select summary here and I right click on a user, I want to be able to drill through to this drill through page and display who I’m looking at. So the trick here is to make this a drill through page. Obviously, I’ve neatened the page up a lot. You can see here in the visualizations, we’ve got add drill through fields here. So I’m going to drag assigned to and put it in the drill through fields. So that does a couple of things. Well, it does a couple of things. One is it puts all the users here, but it also gives me a little back button here, which I’m just going to centralize a little bit because this is quite useful.

Ben Howard: So that’s how I’m going to get back. I also then will right click on drill through and hide that page. I don’t want anybody to get to this page in the Power BI service unless I allow them to do that. So what happens now is under summary, I can have a look at Conor. We can select that data and I can then right click and drill through to my drill through reports. So report all the pages that have been marked as drill through. Plainly, this one has and I’ve got another one in here. So if I click on drill through, then I’m only going to see the task details for Conor McAfee.

Ben Howard: These are all his tasks. We might decide that on this page, everything that Conor has got is completed. So on this page actually, I might put a filter that shows things that aren’t completed. Now, what does this button do? Well, this button allows you to go back to where you’ve just come from. Let’s go and have a look at the stuff for Ben Howard. Drill through. You begin to get the point. In fact, the drill through is on the data item here. So if I want to see the ones that are late for Ben Howard, I could right click and drill through, and that will just show me for ones which are late.

Ben Howard: So we could do this a couple of ways. I could also just have a list of resources in a filter and drill through on that. Talking of filters, I’m going to show you one other thing as well because we do have a filter’s visualization. It’s called a slicer. So let’s click on there. Again, we might just want to see specific plans or who they’re assigned to. So I’m going to put assigned to in this filter. Now, this isn’t particularly pretty here. So I’m going to choose the way I display this filter. I’m going to choose a drop down. That’s nice. And, again, let’s just go into the formatting of this.

Ben Howard: I will have a slicer header. That’s the assigned to. We’ll change that to be 12, keep the font the same. And then have a selection controls, I do like to often show select all as well. And that should work. So now, we can come down here, we can select all of our users or just select Andrea, or we can multi-select using control click and select a few resources as well. So that’s great. I’m going to run out of time to do the decomposition tree. So let’s not worry about that. We’ve created this Power BI report. It’s looking pretty good. Let me just go and select all for that again. Now, I’m going to publish this. So when I publish it, it’ll say, “Do you want me to save first?”

Ben Howard: The answer about it is yes. And then it’s going to go and authenticate me. So where would you to save this to? So which workspace. I’m going to choose my MPUG workspace. I’ll just click select there. And that will go and publish that data up to the MPUG workspace. So whilst it’s doing that, let’s go back to my browser. Here we are in my MPUG workspace. Let’s just hit refresh. In fact, it already tells me that the data sets are there. Here we go. Planner-no visuals. I’ve got a report and I’ve got a data set with it. So let’s just go and have a look at report. In fact, let’s add it to the favorites first of all.

Ben Howard: But let’s go and have a look at Planner-no visuals. I will just minimize that. So here’s my report. It all looks pretty good. I can select a user or a selection of users, and we can go and have a look at the in progress tasks for Ben Howard. We can right click. We can drill through to the drill through page. Note the drill through page is not here because we hid it. So I hid that page. I made it easier to navigate to. We can see the task details and here I’ve got my back buttons go back to the back page. So that’s pretty neat.

Ben Howard: As I say, I’ve already favorited this, if that’s a word, here it is, Planner-no visuals. So let’s just go and have a look at what this might look like on my mobile phone. Let’s just maximize that a little bit. And I’m just going to… of course my mobile phone has gone to sleep, so I need to authenticate to it. And let me open up this little tool here, which is neat. We should see my laptop. And here is my mobile phone. Great. This is live. It’s 17:50 in the UK as denoted by the time on the top left there. Towards the bottom on the right-hand side, I’ve got a Power BI icon. So let’s click on there.

Ben Howard: And already I’m on my favorite. So let me just refresh my favorites just by dragging that down. And somewhere then, we should have a… Oh, I love it when it doesn’t work. Come on. Typical. Of course this works. Oh, here we go. Planner-no visuals bang in the middle there for me. So let’s have a look at that. So here’s my report in my mobile phone. Let’s just wait for that to load up. It’s not loaded up on this phone before. Of course, the phone’s going to go over the web to get that. So it could take a few seconds. Whilst it’s doing that, let me click on the three ellipses near the top.

Ben Howard: Well, next two or three ellipses, I’ve got a share button. So we click on that. Oh, beautiful. Let’s just load that report. Let’s try via ellipsis button next to that. So I can refresh and unfavorite this. Let’s see if that will come in. Let’s just try that again. So Planner-no visuals. Let’s bend Planner-no visuals. Let’s just go for Planner 7.5. It’s probably one that we did before. That one’s going to refresh as well. Live demos. Of course, live demos, demo gods always get you.

Ben Howard: Let’s just come out of there. That should have worked plainly. Apologies. I’m just going to leave my mobile phone to see if this will come back and refresh on there. Let me just put that there loading that report probably here on my desk. So what else can we do from this? Sorry, let me close that application. Great. What else could we do from this report? Well, let’s just say that I wanted to keep an eye on the number of late visuals or the number of late activities that we’ve got. What I can do is I can go into the report and then I can pin this visual onto a dashboard, and I can select an existing dashboard or a new dashboard.

Ben Howard: We will just call this Planner tasks. We could pin that. And notice I can create a phone view for a dashboard as well, but let’s just go to the dashboard. So here’s my dashboard. It’s called late tasks. Where does it exist, of course? Well, it exists in my MPUG workspace. So I’ve now got a new… should have a new dashboard. Certainly not that portfolio dashboard because that’s project count. I’m not quite sure where that’s put that dashboard. Let’s just go back get to that dashboard. Apologies. So that’s the number of late tasks. What can I do with this?

Ben Howard: Well, I can either drill from a dashboard directly to the report, or for something like this I can begin… and these are some of the features that we can do anyway in the mobile app, which I must admit hasn’t come back yet, but we can add a comment, we could chat about this in Teams, et cetera. Because it’s a number, I could set an alert up. So I could add an alert rule and I want to say, I give this a name, but the important thing is that I can produce or have a condition associated with this alert. So I might want to say when we get above 50 or 50 late tasks, then send me an email. And we could happily do that.

Ben Howard: That will then send an email once a day whilst this data is above 57. Let’s click on to the data itself because I might get an alert, and clicking on the alert then takes me directly to the visual. I might like this visual, let’s just go and post that visual up onto that existing task as well or dashboard as well called Planner tasks. So let’s again go to that dashboard. So we’ve now got another visual. We can do a few things around, moving these around and just organizing this and deciding how big they should be, et cetera.

Ben Howard: The dashboards are another way, really, of bringing together data from different reports into a single place. Now, I’ve just turned the mobile data on and off on my phone, and so I’m just going to quickly, in the last few minutes, see if we can get those Planner visualizations coming down onto my phone as well. Now, it still tells me it’s loading for data, which is, I’m afraid, a little frustrating. However, we did see mobile phone usage in one of the other sessions. So quick recap, what did we do? Well for this example, I downloaded this Git file from Power BI Planner. That was the pbit, Power BI template file.

Ben Howard: That will ask you for the location of multiple exported Planner files. We then brought those and opened those into Power BI itself. And what you get out of the box, if you like, is these sorts of reports. So plan by status and priority. They look very much like the Planner reports and a few other nice reports, including tasks, the composition. I quite like this one because we can have a look at our 381 tasks and we can just say, well, let’s decompose those by a certain value. You can see that we’ve got, out of those 381, 360 are medium priority. That’s what it’s chosen for us. So we could either choose one of these other values or we could just choose the high or low.

Ben Howard: Let’s go and have a look at these by which ones are late. So out of the medium priority ones, we’ve got 47 which are late. Maybe I’m interested in those. Let’s just go and see who they’re assigned to. We can see primarily we need to go and speak to Conor to go and get his plans up to speed. So we could go and have at look Conor, and then we could go and have a look at the task names themselves. So we should see 14 discrete tasks in there. So really simple way to decompose your data. Obviously, we then published that file over to our workspace.

Ben Howard: So if we go into workspace here and pick up MPUG, here’s our Planner file, which we can interact with in exactly the same way that we would interact with the file in… sorry, our Power BI file, and we can interact with that in exactly the same way that we’ve done in Power BI Desktop. But, of course, anybody who has access to this workspace MPUG, so anybody that we give access to can now go and view the data. So we can go and give, say, Andrea some access, and we can give Andrea a role.

Ben Howard: So if I just wanted her to view for data, then I would just add her in as viewer role. We can also share individual reports as well. So we could come and select a report and just share that one specific reports of a report Planner-no visuals, rather than the whole workspace. That pretty much wraps me up. I think just as a reminder, this is me, not that you need it. The important bit is the next slide, which is… if I could get on it. There we go. No, there is no next slide. That was the previous slide. So that’s it.

Ben Howard: That wraps everything up that we’ve done in terms of Power BI. We had three specific sessions that we ran through. They were, and they are available on The MPUG site now. The introduction to Power BI Suite was the first one. Deep dive into shaping data using Power Query. And then this last one, creating reports using the Power BI Desktop app. My name is Ben Howard. It’s been an absolute pleasure to run these three sessions for you. Now it’s time to hand back to Kyle. Kyle, how are you doing?

Kyle: Good, Ben. Thank you so much for the session and for presenting the full three-part course. We really appreciate that. For those of you that are claiming the PDU credit for today, I’ll get that back on the screen for you right now. Today’s session is eligible for one strategic PDU. That was the case for all three sessions. And if you missed any of the session or like to go back and review or watch the previous ones, those will be available.

Kyle: Today’s will be available in just a couple of hours. We’ll send you an email with a link to that, give you that on demand. The previous two sessions are available now on mpug.com, and we do have some more sessions coming up on the calendar. We’ll be back in two weeks on 3rd with Ravi Raman presenting a session on transformative leadership and how to elevate your impacts with less effort and none of the stress. And then we’ll begin our next course on March 17th. That’ll be SIPOC workshop facilitation training series, another three-part series three consecutive weeks.

Kyle: That’ll begin on March 17th. Those sessions are available for registration now along with many others. So I just chatted over a link that’ll take you to those and you’ll be able to reserve your seat for those presentations. And with that said, that wraps us up for today. So once again, I’d like to thank you, Ben, for your time and for sharing during these three presentations. I’d like to thank everyone that joined us live or is watching this course on demand. We hope you have a great rest of your day. We’ll see you back soon for another live session. Thanks.


Watch the on-demand recording


Avatar photo
Written by Ben Howard

Ben Howard – 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.  During that time, he’s worked for IBM, DELL, and Microsoft, as well as several smaller organisations. He now runs his own consultancy (Applepark Ltd), providing Project, Project Online and Power BI implementation and training services. He has been awarded the Microsoft Most Value Professional award for Project for the last 13 years, blogs semi-frequently at www.applepark.co.uk, produces video training for Pluralsight and his own YouTube channel, and finally was responsible for producing P2O, an application that exports tasks from Microsoft Project into Outlook.  You can catch him at ben@applepark.co.uk


Share This Post

Leave a Reply