Section 2: Part 1 Level 1: Creating and formatting a table visualization

9. Welcome to Part 1: Visualizations

Well, welcome to part one of this course.

So, now that we’ve installed Power BI Desktop, we can create our first visualisations, but before we do that, it would be useful to see just what we’re going to be doing in part one of this course.

What you’re seeing on screen are the broad categories of the requirements for the PL-300 exam. And you can see there are four sections: Prepare the Data, Model the Data, Visualise and Analyse the Data, and Deploy and Maintain Assets.

In part one of this course, we’ll be largely looking at Visualise and Analyse the Data section. So, we’ll be looking at creating reports and hunting reports for usability and storytelling, and identify patterns and trends. There’s also a minor bit from clean, transform, and load the data. We’ll be looking at anomaly detection and a little bit about developing a data model. We’ll be creating hierarchies using the question and answer feature, and using quick measures.

So, I’ve divided this part one into eight different levels. So, level one is all about creating and formatting a table Visualisation. Level two, we will expand our formatting of it. In level three, we’ll be creating matrices and bar chart Visualisations. Level four we’ll be adding more control to the visualisations. In level five, we’ll be having a look at some of the other visualisations that you can do. While level six will concentrate on mapping. Level seven, we’ll be looking at key performance indicators, gauges, and cards. And then level eight will be things which don’t fit nicely into those first seven levels.

So let’s start with part one, level one, and we’ll be importing data from Microsoft Excel and creating our first Visualisation.

10. Importing from Excel, and Creating Our First Visualization

Now, in this video, what we’re going to do is create our first visualisation, we are going to go through the some of the steps which are needed for Power BI, what we’re going to do is get the data, we’re not then going to do much with the data. And we’re going to create our visualisation. So, those are the three steps you can use, getting the data, transforming it, and then creating your visualisation.

So, first of all, I’m going to get the data. So, in the home tab, we have Get data. And you can see that we have got an enormous array of various sources. Didn’t look enormous, but when you click on the More…, you’ll see how extensive all of these resources are.

So, what I’m going to use is an Excel spreadsheet which is included in the Resources which were in an earlier lecture, earlier article that you could have downloaded. So, if you haven’t downloaded them, please do so.

So, go to click on Excel. And I’m going to go to my resources. Going to open it. And you’ll see there’s a connection. And a new window appears. It says, okay, you’ve got this spreadsheet, but there are a lot of tables. And if you got any named region setup, they’ll also be shown.

So, the table that I’m going to use is the HPIAdmins. What this table is, it’s the house price index for certain regions in England. So, what the average selling prices and that sort of thing, so I’ll click on it, I’ll just check this box. And you can see a preview of the data. So, just to give you an idea, we have the date. This date is being shown in American format. So we’ve got January 1995, February 1995 and so forth. We have the region name, there are six different region names, and we got the average price. And further along, we have got the sales volume the number of units sold.

So, what I’m going to do is I’m going to click on Load, you could edit it. That will take us into another dialogue box, which will have a look at much later because at the moment, I want to concentrate on actually creating visualisations from your existing data.

Now, it doesn’t look like much has happened, except it has on the right hand side, we’ve got this fields pane. Now, you might not see the fields pane perhaps, it might be hidden away. If it is, just click on this little arrow to show it. And what this does, it shows all of the fields all of the columns in the spreadsheet. So, if I were to open up that spreadsheet directly, you would see all of these columns, which have been replicated, except, it’s in alphabetical order instead of the order that we have it in the spreadsheet.

Now, there’s a little symbol next to some of these, it’s called a sigma. That’s the Greek letter –sigma. And if you’ve used Excel, you may recognise the sigma as the summing. So, what the sigma is telling you is that the computer thinks that this is a number which can be totalled together. Similarly, if it doesn’t have the same sigma sign, it thinks it isn’t. So, it might be a date, or it might be some text.

So, what I am going to do is create a visualisation which shows how many properties were sold in each region. So, I have region name. So, I’ll just check it. And you can see instantly what happens. We have region name, and then these six different regions. And I’m going to add to that the sales volume. So, I’ll just check that as well. And here you can see the totality. So, in Greater Manchester, 932,000 units were sold.

Now, we could add additional fields. For instance, maybe I wanted the area code, I’ll just check that in as well. And you can see that gets added, Or, maybe, I want the total average price adding or something like that. Whatever you want to do, just check the box and it gets added.

Now, it’s not exactly appealing at the moment. It’s a fine calculation. It gives me the right answer 3,795,000 sales volume, but it’s not that well presented and there’s nothing about Power BI; it’s about individualization. It’s creating good visualisations being well presented, and we’ll have a look at how to format this table in the next video.

11. Viewing Data

Now, let’s have a quick look around our desktop.

First of all, we have got a ribbon at the top with five different main sections. File, so you can save and load, but you can also publish to the web. Home allows you to get more data to edit the queries and manipulate the data by adding additional columns. View. There’s a very few number of things here that might be of useful. Model, this allows you, again, to add additional calculations and if you click on a field, you’ll get the Column tools, which allows you to add default things like the summarizations, also known as aggregations, or the formatting of particular fields. And then there’s Help. We have some videos and also blogs and that sort of thing. If you can’t see the ribbon, then it may be hidden. Just click on this little symbol up here.

You’ll also have Visualisations and Field panes. We’ll be looking at them in a lot of details shortly. But on the left hand side we’ve got three little items – Report, Data, and Relationships.

Now, Relationships is useful when there’s more than one table, not so useful with just one table. So, we’ll skip over that for now.

Now, Report is where we are going to be spending most of our time immediately, creating visualisations. By the end of this visualisation section, you’ll be able to create so many different visualisations that we can then concentrate on manipulating the data or transforming the data, and you’ll be able to use all of this section very easily.

And then there’s Data. This Data allows us to see what data is actually in a table. So, it works fairly much like an Excel table. In that we’ve got scroll bars, we can scroll both to the left and to the right, and we’ve also got filters. Now, these filters allow us to see a particular date for instance. So, for instance, if I wanted to see everything that happened on the 1st of January, 1995, then I would check Select all to get rid of the lot, and then check that one particular date and click OK. And that reduces the fields that I’m seeing, the rows that I’m seeing. So, it doesn’t reduce the fields, it reduces the rows. And you can see at the bottom what’s happened. There are 1,584 rows, but I’m already seeing six of them. And it also tells you something about each individual field. This date column has got 264 unique distinct values, but I’m only showing one of them.

So, just like Excel, I can go for a range. So, for instance, I can say I want all of those items, and to do that, I went to Date filters, Custom filter. All of those items which are on or after a certain date and is on or before another date. So, in other words, it’s between dates. So, I’m going to go for the first of the 1st 1996 to the last day of 1997. So, that would be 12/31/1997. I’m currently formatted to the American format, date format. So, here you can see all of those dates that I’ve selected. So, I’ll just clear this filter, I could also clear all filters, and it will show me all of the data.

Now, I just want to emphasise that selecting a filter doesn’t actually change your data. Your data is still there, it’s just that in your current view right now of the data you are just seeing fewer datasets. So, any visualisations when we create them will be created on the full dataset and not just what we are seeing.

When it comes to text, we’ve got a similar sort of filters: Equal…, Does not equal…, Starts with…, Ends with…, Contains… and Does not contain…. And with numbers, we’ve got things like Greater than…, Greater than or equal to…, Less than…, Less than or equal to….

You can also sort the data. So, for instance, I want to see everything that happened by a particular date. So, now we’ve got all of the things that happened in January the 1st together, and then February the 1st together. There is one other thing I should point out about this data. You can’t edit it. So, I can’t type anything into it. That’s because Power BI, unlike Excel, expects the data, once it’s been transformed, to be right, and it’s not going to allow these ad hoc changes that you can do in Excel. So, if you need to change the data, in other words, if the source data is wrong, you need to do that in the original source data. You can’t do that in Power BI.

So, whenever you’ve got a question about what you are seeing in your visualisations, you can always go to your data pane, the Data button here, and just have a look. You can filter down for a particular item and have a look at your raw data. Right. In the next video, we’ll have a look at actually creating our first visualisation.

12. Focus Mode and Different Visualizations

Now, in this video, we’re just going to look at two things. Firstly, this is a bit small at the moment and we’ll have a look very shortly at how to genuinely increase the font size. But suppose you actually had a visualisation which was this small. How can you best work with it?

Well, you see this little icon down near the bottom. Now, what this is called is Focus mode. Now, to activate it, you need to click on the visualisation so we’ve got all of these little markers around and now we can click on the Focus mode icon. So, what this does, it removes any of the visualisations temporarily from what you’re looking at, it removes everything, all of the distractions and allows you to concentrate on this one visualisation. If you want to go back, just click on Back to the report and you get back to what it really looks like. So, it allows you just to avoid a lot of clutter.

Now, the second thing is, well, we can do tables, what else can we do? And I just want to briefly show you some of the things that we can do, very quickly, we’ll go into much more detail a bit later. Over here, we have got the Visualisations pane and you see that we’ve got this particular visualisation selected and we have the table selected over here. Now, we can change the visualisation very simply by just clicking on a new one. So, if I wanted to change it to a stacked bar chart, all I’ve got to do is click on it and instantly it changes. And you can experiment with lots of different types of charts to see what might actually work with your particular data. So, you can have a pie hart, a donut chart. There’s some which won’t really work with the data that you’ve got. This is a scatter graph but for this we need two numeric values and we’ve only got one. So, you can have a play with all of these just see what there is.

Now, for now we’re just going to concentrate on our table. A lot of the things that we’re going to learn in the next few videos are relatable to all or most of these visualisations and it just makes sense to learn the mechanics of how you can do things in Power BI by having one constant visualisation and then looking to see how this is changed in other visualisations.

So, in this video we had a look at the Focus mode. And we had a look at how to change one visualisation for another. And if you don’t like the results, you can always undo. Now, just bear in mind, if you’re going to change a visualisation you must select it first. If I click Away, nothing’s selected, and I click on a visualisation, it thinks you’re trying to create a new one which is not, in this case, what I want to do. So, I’ll just delete that by pressing the Delete button and then I’ll click on my visualisation and then I can change it to whatever I want.

13. Why Do I Need a Work Email Address? How Can I Get One, if I Don’t Have It?

Now, to sign into the Power BI service, which we’ll do at some point during this course, it is necessary for you to have an email address.

Now, unfortunately, not all email addresses will do. And if I just type in Power BI login, for instance, and go to sign in, and there are plenty of ways to sign in, this is just one way. And I can type in a work address. So, let’s just type in, john@gmail.com, for instance, you will see, that it says, you entered a personal email address, please, enter your work email address.

Now, if you do have a work email address, then I suggest you use this and you can skip on to the next video. If, however, you have a Gmail, Hotmail, Outlook.com, and many of this, which are non-work email addresses, then you’ll need to either use a work email address or get a work email address.

Now, before I give you an idea of how to get a work email address, or what Microsoft believes as a work email address, there is a question. Why? Well, suppose my email address was philip@filecats.co.uk. The idea of Power BI, especially at the Pro level, is to share. And so, I would be able to export anything I make to any email address and invite them to sign up for a Power BI account of their own. But alternatively, I could have a user philip@filecats, john@filecats, margaret@filecats. And the idea is that, I could produce a report or dashboard and share it with others in my organisation. So, what is my organisation? It is all of the people that are users who shared this domain. So, @filecuts.co.uk, in this case. So, that’s why it doesn’t want you to use for instance, Gmail. So, if I was using Gmail, well, I’ll could then potentially share it with everyone with a gmail.com account. But that’s not the idea, gmail.com, outlook.com, they don’t show a particular organisation that you’re in, they show, instead, an organisation that owns the email address. So, if you do have a valid work email address, then I suggest you use it, you can use it for testing purposes. So, if I have an email address and this is a very big company and over people are in this domain, also known as a tenant, have used this, well, that’s probably fine.

So, let’s say you don’t have a work email address. And this is something that people have written to me about what can you do? Well, there are two alternatives. Now, the easiest alternative, which doesn’t cost any money, is to sign up for a free Microsoft Office 365 E3 or E5 account. Now you can get a free 60-day trial. So, if I click on any of these, so this is Office 365 E3, you can see that it normally cost about $20 per user. There is also an office 365 E5, which cost a bit more, at some $35 per user. But note that there is a difference. Make informed decisions with data analytics. So, if I have a look at the differences between E3 and E5. So, the above all given you by Microsoft Office, they all give you email and address and this is the email but what we need is a work email address. But if I go down very far-far, you can see here that we have advanced analytics. One of these to the E5, gives you Power BI Pro. Now, Power BI Pro, this is the professional version of Power BI. Its major difference is when you want to share reports.

So, you can either sign up for an office 365 E3 or E5. And the great news is if you try it for free, then you can do it for 30 days, 60 days and then after that, you don’t have to pay for more. But you still have the email address set up. Even though you don’t have all of these particular benefits, you will still have an email addresses set up, according to Microsoft, and it is that email address that we can use to sign into Power BI. So, if you don’t have a work email address, then why not sign up to Office 365 E3 or Office 365 E5 with a free trial?

Now, if you are using Microsoft Azure, and you have access to it in terms of being able to create new directories, then an alternative is to create a new organisation name. However, I think that not too many people will be using this. At the end of the day, you will get an email address called something.onmicrosoft.com. So, it could be john@something.onmicrosoft.com, and this will then be your email address that you can use as a work email address.

Now, if you’d like more information about this subject, then I have attached to, as a results to this lecture, a video by people called Guy in a Cube, which go through these three different options: use a working email address, sign up for free trial of Office, or use Azure Active Directory. So, if you want to see more details, then, please, have a look at the Resources and have a look at this video. So, now hopefully you have got your email address. We’ll move on to the next video.

14. Saving Visualization to the Desktop and to the Power BI Service

So now, let’s save our first visualisation. We can go to File, Save or Save as, and just name it: MyFirstVisualisation. Now, it is a Power BI file – .pbix. You can also have it as a template file, if you’re creating a template. We’re not going to do that for now.

So, now it’s saved. You can close it. And so, I will now open it up again. Power BI Desktop. And then I can go to File, Open and open it up again. And there you can see it in my recent items. So, I went to the Exit, right at the bottom, previously. So, now I’ve opened up my visualisation, and there it is.

Now, what I can also do is publish it to the Internet. So, I can go File, Publish. So, Publish to BI Service. So, if I do that, then it asks me for my login. Now, I haven’t yet got a Power BI account. And you’ll see that if I enter in my username or my email address that I did enter when I was setting this up a couple of videos ago, it says “It seems you don’t have an account for Power BI.”

So, what it does is it launches the Internet. So, I click Sign up. Prove. You’re. Not A. Robot. So, type in your telephone number. And it goes through a… It sends you a number which you then have to put in. So, I’ll just skip this on this video. And now I can click Sign up. So, Create your account. By creating the same information that we entered into, entered when we set all of this up, I don’t think we set up a password. So, it’s going to send yet another verification code. So, I’ll type that in. And the region. So, can Microsoft send me promotions, offers? Go on them, then click Start. And just to let you know, passwords must be between 8 and 16 characters, combine upper and lower case, letters, numbers, and a symbol. So, a very complex password is needed. And also it can’t contain a part of your email address.

So, once you’ve gone through that hurdle, click Start. Microsoft then creates your account. It then invites you to send this to more people. I’m going to skip that. And there is my account set up. And if I go into subscriptions, you can see that I have a licence for Power BI, and in the app permissions, you can see Power BI as well.

So, if I go back to the Power BI Desktop, I can now Sign in. Again, you can go to File and Publish. So, type in your account. And after a few minutes… And it does take a few minutes for Microsoft to set up your account. So, if at first it says you don’t have an account, just wait a few minutes. Type in your password. And then you can publish this to Power BI. So, click Select. Publishing MyFirstVisualisation to Power BI. And there you are, we can now click on it. We can open Power BI. So, this opens up a new web account, a new webpage. I’ll sign in and there is my first visualisation online

Now, admittedly, it’s not a brilliant visualisation but what we have done, we have saved our visualisation to our desktop using Power BI and we’ve also published it to the web.

So, we have now got all our accounts set up and we can now publish further and better visualisations onto the Internet. We’ll have a look, a fair bit later, at the Power BI on the web, but for now we’re going to continue using Power BI on the desktop and create even better visualisations.