1. Downloading Power BI and creating visualizations from Dataverse data
Hello. And in this section we’re going to be looking at Power Bi. Now, Power Bi is a data visualization tool and it’s part of the Power platform. So I’m going to start by looking at Power Bi desktop. There is also the Power Bi service that we’ll be looking at later on in this section. So Power Bi desktop, completely free, allows you to visualize data both on the Power platform and off it.
So you can use it for Excel or lots of different sources. So here you can see, I’m just downloading Power Bi desktop. It’s available for the PC. It’s not available for Mac, unfortunately, but the Power Bi service can be used for Macs. So you can see it would just take a few seconds to download. So I’ll just pause the video here. So here we go. We can see it’s downloaded.
This is now installing and it’s installed very quick. So in terms of the actual installation. So let’s just launch it now. If you don’t have a PC, don’t worry, you’ll be able to follow along with what we’re doing in the next few videos and be able to replicate a fair bit of it in the Power Bi service. So first of all, we need to connect to a data source. So I’m going to connect to the database because that’s what we’ve been using for the rest of the course. So it says it needs an environment domain and it gives an example orgNAME CRM dynamics. com.
Well, we can get that from make Powerapps. com the Powerups portal. First of all, make sure you’re in the right environment. I’ll show you the difference and then go to session details. And it’s this instant URL we’re talking about. So it’s everything between the slashes. So don’t include Https and don’t include the slash at the end.
So you notice what this is. It ends with C one A if I change to a different environment. So it’s a completely different URL. So I’m going to right and click on that and copy. So let’s paste it in. And I’m going to use the data connectivity mode of import. That means I want all of the data at the beginning, not when it needs it. So let’s click OK, I’m not signed in.
Click sign in and I’ll select my account and enter my password. So now I’m signed in, click Connect and it will now bring up on the left hand side a list of all of the different entities or tables. So select the table that you want to use. So I’m going to have a look at the expense, which you’d think would be about here, but it’s got the prefix. So here you can see it off here. So I’ll click that and I’m just going to go to Lord. We’ll have a look in the next video about transforming data. So you can see the sort of things like the expense value and the descriptions we have previously had a look at. This is only a preview, so it’s not showing all of the information.
Now it’s loading and you can see there are pending changes to your queries that haven’t been applied. So it’s now creating a connection in the model, loading the data to the model. It shouldn’t take too long, it’s not that many rows. And then if I go to the right hand side to the fields pane, and if you can’t see it, then you may need to expand it. I’ll expand my table and there we can see everything. So let’s create a visualization. So let’s create a column chart. For instance, I’m going to create that one and as soon as I click on the cluster column chart, it appears. Now it’s got no data and I can resize it, I can drag it around, I can do things like that. If I’ve clicked onto it, you can see these eight markers on the side. And once I can see those, then I can drag fields into here.
So let’s have a look at the account name in the axis and the expense value in the values. So instantly we get a visualization here and you can have multiple visualizations on the one page and you can have multiple pages in the report. So I’m just going to save this. So go to file, save as, and I’m going to call this a Power Bi visualization. So in this video, we have downloaded Power Bi desktop.
We’ve opened it up and we’ve connected to the database, found out where you get that connection details from. You have to go to session details and it is the instant URL, everything between the slashes, but not including. And then once you’ve done that, you can select your table, the fields. Come over here. These are the columns in your table. You can then create a visualization and then populate that visualization with your fields.
2. 60. Create Power BI report by using Power BI Desktop
Now in this video I’d like to talk about some of the characteristics of Power Bi desktop. So you won’t be required to know all the ins and outs. Indeed, there’s an entirely different certification for that, the DA 100. But let’s just talk about some of the things it can do. First of all, you get data. So you can get data from a huge variety of different sources including Microsoft Access, Azure, the Data verse as we’ve seen Dynamics three, six, Five, Excel files and folders in your computer, HDFS that’s the Apache hadoop distributed, file system Python R and SQL Server.
So you can see some of the categories on the left hand side files, Databases, Power Platform, Azure and other online services. So you can use these data sources when creating your reports and of course when refreshing them, you can create reports on a single page, you can create visualizations and you can create cross report drill through reports. So what do we mean by that? Well, let’s have a table and this table is going to show things like the date of expense and the expense value.
So things like that. So what I can do is I can put the account which is mentioned here as something that I want to drill into. So maybe I want to know more about the 7035 items or dollars which are there for the Alpine ski house. So I’m going to add the account name. I don’t actually need to do that. So we can see if I expand it, that the Alpine ski house is there and I can add a drill through field of the account name. So if I now go back to my original page, I can right and click on any of these and I can drill through to my second page. And now it will only show those things which are to do with the Alpine ski house. So that is called a drill through report.
Now, with regard to your data, if I click up here and go to edit query, you can also get and transform the data. So here we can see it in the Power query editor. So what we can do here is add calculated columns. For instance, so I could, if I wanted to get to the expense value which is over here and say I want a new column which is this multiplied by 20%, which we have used in the past.
So multiplied by 20%. And here is our new column, this multiplication column, and I can rename it, for instance, 20% of expense value. Now this uses a language called M. You won’t need to know that language or anything like that. In this particular course, if I scroll right to the top, you can see that 20% of expense value is there. Now, additionally, what I can do is I can look at the data and I can also create a data model. So if I had more than one query, I can join them together. I can also create calculated columns and calculated measures, measures of things that sum average, that sort of thing.
So creating a calculated column, very similar to what you can do in Get and Transform. There is a bit of an overlap except Get and Transform uses the M language and creating a calculated column here uses the DAX language, DAX. So if I create a new column, suppose I wanted all of these dollars to be in pound sterling. Then I could say expense value in pound equals the expense value divided by 1. 4 as an example. So that creates a new column and we can see the new column if we go to the data.
And I probably want to change this so that it is now in pounds sterling rather than in dollars. So there we go. Now that’s a calculated column, calculated measure, well, that creates a single roundup figure. So something that is already sum dorogated. So just as an example, in a very trivial example, I could say total measure or total expense value in pounds equals the sum of the expense value in pounds. Usually they’re used for much more complicated measures, but that is one way that we can use it. And then I can create a new visualization and drag that in to the data fields, for instance. So that is just one example, very simple example, but that is how you use calculated columns and calculated measures.
Now you can’t create dashboards. We’ll have a look at what dashboards are in the Power Bi service. You can’t access this in any way other than through your PC desktop or laptop. You can’t share your Power Bi desktop visualization unless you give them the file. But there are some security things that you can change. So you can see, for instance, you can have a look at certificates and you can have a look at privacy and that sort of thing. So this is the Power Bi desktop, a huge number of connectors. You can create visualization, you can create drill through reports, you can get and transform the data. And you can also develop models including calculated columns and measures.
