PL-300 – Section 38: Part 4 Section 7 – Other Power BI Service Topics

281. Create a PivotTable from a Power BI Dataset in Excel

Now, suppose you didn’t actually want to export the data, you just wanted to do an analysis in Excel, namely a pivot chart. So that’s equivalent of a matrix in Power BI.

Well, you can do this in Excel fairly easily. But you might need the latest version of Excel to do this. So, you’ll see if you’ve got the latest version, if you go to Insert and in PivotTable, you can see that this is a button of two halves. If we go to the bottom of this, you can see that we’ve got make a PivotTable From Power BI.

Now, this is also available in Data, get Data, From Power BI. You can see again, it says create a PivotTable connected to a Power BI dataset. So, let’s do that. So, we were looking at the report called A. So, we’ll continue to do that. So after clicking from Power BI, you might be asked to sign in. There’s an awful lot of signing ins. And then you can see that we have got certain datasets. And I’m going to have a look at the dataset A but you’ll also notice that we’ve got all of these endorsements that we have done previously. So, if I click on dataset A, then it builds a blank PivotTable, and again, you might be asked to sign in. We have got the two tabs here. So, the top tab again takes us back to the Power BI dataset. So, if we wanted to create another PivotTable, we can do that quite easily.

Now, the rest of this gives us the standard PivotTable, except that all of the tables are shown separately. So, you can see, I’ve got my category table distinct from the product table, distinct from the subcategories and so forth.

Now, if you’ve ever used Pivot Pivot in Excel, if you’ve ever used SQL Server Analysis Services, the Tabular DAX model, then you’ll have seen before that we’ve got all of these tables separated in Excel. If not, it’s basically what you’ve got in Power BI. You’ve got the separate tables, and you can select individual items. So, for instance, suppose I go into the DimProduct and I have a look at the StandardCost. So, I’ll drag that into the values, and then you run into the major problem for creating PivotTables from Power BI datasets, at least at the moment. What happens in, for instance, Power Pivot, is that when you create a model in Power Pivot and then create a PivotTable in Excel based on the dataset, that it creates measures itself. These are called implicit measures. However, currently, going to Insert, PivotTable From Power BI, it only supports explicit measures. In other words, ones that you have already defined in DAX, D-A-X in Power BI, then uploaded into the Power BI service, and then are using in Excel. It currently does not support drag and drop aggregation of numerical fields. And that’s a pity because everything else that’s here, you can just drag, for instance, FrenchProductName into rows or you can have a look at a date. So, we’ve got EndDate here. And drag that into columns. And you can see it works perfectly well. Unfortunately, you have to predefine the measures. You can’t simply find a measure that you want to include and have it created implicitly. You have to actually have predefined measures.

Now, needless to say, users need to have permissions for the underlying dataset, so it’s not the case that everybody in your tenant, in your organisation will be able to connect to this particular dataset. You do need build permission for the dataset. So, that could be a workspace or an app or you need to have a remember all in the workspace containing the dataset. Or you might have a report or dashboard shared that uses the dataset. In other words, they have to have some way of being able to access the dataset.

So, creating the PivotTable, fairly simple. We can go to Insert, PivotTable From Power BI and then we get this pane of Power BI datasets. And you’ll be able to say I want to create a dataset based on this particular one. And if you’ve got the measures already set up, explicitly set up in Power BI, then you can then create your own PivotTables without having to export the data into Excel first.

282. Use or Create a Dataflow

Now, the experience of getting data has in Power BI Service not been that great. So, we could get files such as Excel or CSV, and then press this little arrows. So, you can see if I click on any of these, it says, use desktop, not a good experience. So, this is why Microsoft has been creating dataflows. Dataflows allow you to connect to more than just these few data sources. So in Get Data, I can click on, Get started.

Now, I can also get there by going to a Workspace, and you need to have a Workspace for this, It will not work in my workspace. I’m going to New and Dataflow. So, however, you get there, you get these four options. So, let’s just ignore this first option for now. The second option, link entities. So another name for tables, from other dataflows. So, if you got a dataflow in your organisation this can help maintain consistency, but you need to have your first dataflow to be able to do that. You can choose to, import a dataflow model. So, you do that by going to a dataflow, exporting it as a JSON, and then re-importing. But again, you need to have a dataflow for that to happen. And then this fourth option is good if you’ve got an Azure Data Lake Storage Gen2 account. So, if you’re using Azure and you’re using the Data Lake Storage you can attach what they call a Common Data Model.

Nowadays from November 2020, that’s being called a Dataverse and I imagine the terminology will change. So attach a Dataverse folder from your Azure Lake Storage account to use it. So all of those fairly help, especially if you’ve created your first dataflow. Otherwise, we have to go into this one and this is defining New entities or Tables. So, I’m going to add a new entity and just have a look at the array of options we’ve got. So, for Files we’ve got Excel, Folders, JSON, PDFs, Parquet, SharePoint folder, Text CSV and XML. Doesn’t this sound like the range of options which were available in the Power BI Desktop? So, here we have a database. Azure these year functions are really cut down the data sources but they’ve still got the key ones like Azure SQL Database and Azure Synapse Analytics and the Data Lake Storage Gen2. We’ve got a few online services, most notably SharePoint Online or Microsoft Exchange Online. And then we’ve got a few others.

Now, what I’m going to do is I’m going to connect to my Dataverse from a Power platform. So, I don’t expect you to be doing this yourself unless you are using Power Apps. So, I’ll go to my Power Apps, here it is. And I’ll go to Session details, and here is the instance URL. So previously, when we’re talking about Power BI Desktop, I said just to copy this small bit, for the Power BI Service you have to copy the big bit. And so, they may, at some point Microsoft may allow you to import the same details but not just yet. So, I’ll copy that in, click on my Dataverse, and so that is my server URL. If you’re connecting to information that’s on your system you will need a data gateway and you may need to authenticate as well. So, I’m going to sign in to Power Apps. So, I just need my password, and sign in. And now I’m signed in. So, now I click Next. I can now have a look at the entities or tables that I want to import. So, I’m going to import this one. And notice this is trying to be Power Query on the internet, is a good first attempt at it.

Now, that this will develop as time goes on. So, I’ll transform the data, and look at this. This is getting me into something that is much more manageable as Power Query. So previously we didn’t have any of these options available. But this is really Power Query going to be on the web. So, what I’m going to do is I’m going to take these two columns and I’m going to remove the other columns. So, you can see it being added to our query settings. So, now I’ve done that, I will save and close. Computer’s now making sure that everything is fine so that we can use it. So, I saved my dataflow. So, I’ll call this the Dataverse accounts. So, there we go.

So, we now have this new entity called Account. And you’d think that the next step would be to take this entity and create a report on it in the Power BI Service. However, when we look at the actions we’ve got, Edit the entity. We have got, Apply a machine learning model. We have got Incremental refresh. And then we have got some properties which is just a description. So no, that’s not the next step unfortunately. The next step is to use this back in Power BI Desktop. So, I’m hoping that at some point we will be able to use this dataflow in the power Power BI Service but it’s not there yet unfortunately.

Now, just one word before I go out of the Power Power BI Service. If I were to go into My workspace and then Get Data, you’ll see that the dataflow’s connection is not there. I have to go into a workspace other than, my workspace. So, I will need pro premium capability and here are my dataflows. And then to get back to where I was, again, you’d think that clicking on Get started will get us back there but I can’t edit any entities. Instead, I need to go to the Workspaces, go to my specific workspace, and here we have my Dataverse account dataflow which I can now refresh or schedule refresh, or export as a JSON. So, this is when I can export it and then re-import it say, in a different user or, maybe, a different environment. Maybe, I’ve got multiple Power BI Services for some reason. So, what I’m going to do now is open up a Power BI Desktop and connect to this flow. So, I will go get data, and Power BI dataflows. So establishing a connection I’ve got to sign in and now I’m signed in. I can see that in project 001, we have my Dataverse accounts and within that we’ve got Account. So in this video, we’ve had to look at dataflows in Power BI Service.

So, Power BI Service dataflows not to be confused with Power Automate flows, which are much, much more extensive in Power BI. The dataflows are the equivalent of the Get Data in the Power BI Desktop. So, we link to some data, and eventually if I add a new entity you will be able to see that we can get in data from lots of different data sources, maybe, using a gateway and we’ll be able to use a Power Query type interface to be able to refine our data.

283. Creating Scorecards and Metrics

In this course, we have been looking at lots of different reports and dashboards, but how can we join them all together? Well, we can do this using a scorecard.

Now, if I go into metrics and have a look at a sample scorecard, you can see that a scorecard is filled with lots of different types of metrics. So all of these are either linked to data or are manually entered. And you can see that there is a trend among these metrics and a status. They also have a description and a due date. So, a scorecard brings together lots of different metrics together. So, let’s have a look at how we can create our own scorecard. So go to metrics and new scorecard, or create your own scorecard. And you can see here we have a blank scorecard and it’s inviting me to create my first metric.

So, I’m going to say percentage progress. So, we’ve got an owner, so this is me, and we have a current value. So, I can either enter a value in which case this would change to a manual metric, or I can connect to data. So, I’m going to have a manual metric, so I’m not even going to click on that. I’m just going to say that it is 10. In this case, say 10%. And I’ve got this formatting bar at the top, so I can say it is currency, decimal or whole number, percentage or scientific. I can also have a look at what currency I would want to use. Or other formatting options. So, I’m going to say this is 10%, so I now just have to reenter this as no 0.1 to create 10%. So my final target, well, I want it to be complete, so 100%. My status, well I’ve got default statuses of overdue, behind, at risk, on track, not completed and started. I’m going to say that this is on track and we’ve got a start date and an end date. So, this is my metric. I’m going to click save. And here you can see my metric as it is displayed.

Now, you can see there are lots of other options. I can add some notes, edit the metric, which takes me back to this screen, or clicking on the dot, dot, dot, I can see details which allows me to see a history of some sorts. So, I can narrow this bar closer if this truly had a history. I can also show the target. I can update the value, see the history, add some automation for the status, so, maybe, if it goes to 20%, I can change the status of something else or click new rule. So, if a value or the date or the change is different to or equal to an absolute figure or the percentage of the target, then it’s status should be this. If not, then change it to this. So, I’m just going to bin that status. And then we’ve got some display options. So display, show the change every set number of days, like month on month or month to date, year to date, or just the difference between the current data point and the previous one. And then display as percentage or an absolute value.

So, let’s add another metric and I’m just going to call this overall and at the moment I’m not going to set up anything in terms of current value or final target value. Instead, what I’m going to do, is click on it and add new submetric, or I could check the box and go to new, add submetric. So, this makes overall a header and then I have a new submetric. So, I’m going to say number of bikes sold as a metric and I’m going to take this from another bit of data. So, I’m going to connect to data. I’m going to have a look at my category and sub-category report. Click next. And say, okay, what exactly do I want it to connect to. Well, I’m going to connect to this, the touring bikes and you can see the current figure 3.8 million for the sales amount. So, I’m going into a report and saying I want it to be that very specific thing. And then when the data gets refreshed, then our metric gets refreshed. So, now it is connected. So, I want a final target value, let’s say of 5 million. So, this is one metric. I’m going to add another submetric.

So again, I’m going to say number of bikes sold. I’ll change the metric name in just a moment. I’m going to connect to the same report, but I’m going to have road bikes. So, the previous one is touring bikes, so this is road bikes. So, I’ll click connect and I will now say road bikes and I’m going say my target 15 million. So, let’s click save. So, this other one, I’m going to change to touring bikes. So, what do I do with this not submetric, this main metric? Well, I’m going to edit it and I’m going to say that it should be a roll-up. I should use the submetrics that I’ve got number of touring bikes sold and number of road bikes sold and I want to add them together or average them or take the biggest or smallest. So, I’m going to add them together for both the current value and for the target. So, now you can see that my main metric, which is built up of these submetrics, is 18 million out of 20 million. And I can hide the submetric if I so wished to. So, you might have a scorecard with lots of different metrics. And the way you can filter it down, is by just looking at those which are on track or completed or anything else. So, let’s say I want to edit this and say that this one is behind. So, I want to see all of those which are behind schedule. I can do that just by clicking, get the word behind.

So, these are scorecards and metrics. So, very easy to set up. If I go into metrics, I can set up a scorecard by going to new scorecard. If I scroll down, I can see my existing scorecard, which I can then go into, but it’s important to note, when I do this a second time like I’ve done it just now, it is not editable immediately, it is in display mode. Instead, I can click on edit and then I can do any editing that I wish to do. So, I can go back to read mode and now I can only add notes. So, this is how you create a scorecard by going to metrics.

284. Sharing Scorecards and Metrics

In the previous video, we had a look at how we can create scorecards, which use metrics. In this video, we’ll look at how we can share them. So, if I hover over this, first of all, I can open it by clicking on the scorecard. Secondly, I can click on the dot, dot, dot, and go to Settings. So, this allows me to change the scorecard name. I can also do this by clicking on the scorecard, going into Edit mode. And then I’ve got a pencil marking here, so I can edit the scorecard here, but I can also do it by clicking on the dot-dot-dot (…), and go to Settings.

So, here I can rename it, for instance, “My Scorecard.” You can give it a description and a contact email address. Scrolling down, you can also endorse it. So, you can say it is to be promoted or even certified. So that can help others find your content. You can also apply a sensitivity level as well. But as you can see, some sensitivity levels are not enforced in Power BI. So having it as a promoted or certified does help share it with others. You can add it to your own favourites, you can also delete it and you can also share it, either by clicking on the share button here, or managing permissions here.

So, let’s click on the Share button. And here you can see, you can have people in your organisation, or specific people have access to this scorecard, just like it was a report. So, you can allow recipients to share this report, and allow recipients to build content with the data associated with this report. In addition, you can go to Manage Permissions. So, here you can now add links and give people direct access. So, you can add users here or you can add links here. This takes you to a similar dialogue box to what we’ve had before.

Now, if I go back to the scorecard and click on Share, you can see also that there is a dot-dot-dot (…) up here for Manage Permissions, which takes me into here. So, we’ve got the list of links giving access, and direct access here, and I can also grant people access by clicking on that Plus button.

Now, another way of editing permissions is to go into the scorecard. So, I’ll need it in Edit mode, and then I can go to Settings. So Settings, this is another place where you can change the name of the scorecard, the description, contact, time zone, and whether team notifications are on or off for scorecard collaborators. But you can also go into permissions and you can add members to an existing role or create a new role with custom permission settings. So, this is a lot more granular, so you can change not just who can see this scorecard or not, but you can also say who can view particular metrics. After all, you might not want everybody to be able to see a particular metric and who can update the current value and the status. Again, you don’t want necessarily to give that to everybody. So, you can see we’ve got existing roles of View All, and View and Update All.

Now, you can select any role as the default, and you can also delete a role by clicking on the dot, dot, dot. So, this could be a role, which is just based on the number of bikes. So, I will have this as the bikes’ metrics, and I can add my role members here. So, I will add Jane User here, and I will allow that person to view the number of touring bikes and the road bikes.

Now, that person also needs to have scorecard access. So, it’s not good, them being able to see a particular metric when they can’t see the scorecard. So, this is another way of making sure that only the right people see the right things. So, if I close that and save it, so you get to that in Settings and Permissions. The third tab incidentally, allows you to adjust the statuses which are available. So, by default there are six statuses, but they might not be suitable for what you want, so you can add a new status.

So, there are lots of ways that we can share your scorecard, either in part or in whole. In the next video, we’ll look at how we can use, either the scorecard or metrics in your own report.

285. Using Scorecards and Metrics

Now, you might be wondering can I use such a scorecard in my own reports? And the answer is yes, you can.

If I go into my workspace, for instance and go into an existing report, say this category and subcategory report, which I’ll go into a spare page. So page two for instance, I’ll edit it. I have a trophy here, and that is my metrics. So, if I click on this, I can then create a visualisation. I can add metrics into this visualisation. So, I can either have it as a straightforward list. So, if I say Yes, please add metrics as a list click browse and there is my scorecard. And click back to report. You can see that it has created a visualisation which has the entire scorecard which I can then resize as appropriate.

Alternatively, if I just want to use the actual metrics themselves I can click on the trophy here, click add metrics and add a metric as a visual. So instead of metrics, it’s just one metric. So, I will browse my metric again go to this scorecard, choose a particular metric. So, let’s say percentage progress, click add to report and there is my metric.

Now, I can do this in Power BI, Desktop as well but I can only do it using data from the Power BI Service. So, if I go to Power BI Desktop, and if I’m logged in I can click on my metrics and have exactly the same experience. So, I can add a metric as a visual or add metrics as a list and select my metric. And here it is.

Now, there are plenty of properties associated with this metric. So, if I go into format your visual, first of all we’ve got where my connected scorecard is. So that is a web address. We have got whether we can see the visualisation so we can turn that on and off, and we can address things in the metrics such as the scorecard name that you can see at the top, the current value. So, you can change the font size, for instance how you can see it or the colour, the target value and other things such as growth, honour the metric name, the status, the due date and notes. And now you’ve got this as a reporting Power BI Desktop. Then, you can save this and upload it to the Power BI Service if you wish.

So, this is how you can share metrics. You can incorporate it into your own reports by using the metrics visualisation. You can do other things like chat in teams about it. And you can go to the metrics and say I want to share this, or I want to manage permissions.

Let’s update our metric that we are using. So, I’ll go into the scorecard and edit it and go into see details. And I will scroll down, new check-in. So, the value for the 10th I’m going to say it’s now up to 15%, 0.15. I can include a note. And now if I go into my report, sales category and subcategory you can see that the metric is automatically updated. It won’t be updated in the scorecard yet until tomorrow because I’ve put a future date. But hey, you can see it is reflected in this visualisation. And if at a later time I go into my Power BI Desktop report you can see that the graph is also updated. Instantly there are a few more things you can do in this visualisation. You can edit the metric by clicking on this dot-dot-dot (…). You can open the scorecard and open notes as well. So, open the scorecard, opens it in a web browser and go to Edit Metric. That allows you then to see the scorecard for this particular metric which you can then edit as you want to do so.

And finally, if a particular metric is important to you then you can click on the follow icon and then it’ll be added to your list of metrics that you are now following. So, you can just click on following and see all of your important metrics.

So, this is how you can share and use scorecards. And in fact, in the workspace they now are just shown just like any other report.

• Category: Uncategorized