DA-100 Microsoft Power BI – Level 1: Creating and formatting a table visualization

1. Welcome to Part 1: Visualizations

Well, welcome to part one of this course all about visualizations. So now we have installed Power Bi desktop. We can create our first visualizations. But before we do that, it would probably be useful just to see what we’re going to learn in this part one. What you’re seeing on screen are the requirements for the DA Dash 100 exam and you can see that they are broadly in sections. We have got sections one to three which are get and transform using the M language that will be the subject of part two of this course.

We’ve got Modeled the Data, which is uses the DAX language, which is part of part three of this course. Though I think one of these actual items, define Quick Measures is actually more useful in part one, also part of part three as well, you can think, but I think it’s quite useful when discussing the visualizations. Then we’ve got visualize the data, analyze the data and deploy and maintain deliverables. And that is a mixture of part one and part four. Part Four being the Power Bi Service So creating dashboards that is exclusively a Power Bi service requirement as is deploy and maintain, deliverables. Most of the rest of this are to do with visualizations and that’s what we’ll be covering in part one of this course.

So we’ll be creating reports, we’ll be enriching reports for Usability and we’ll be enhancing reports to expose insights and performing advanced analysis. Now, I’ve divided this part into eight different levels. So we’re starting off with level one, which is creating and formatting a table visualization level two will be formatting the visualization. Level three creating different visualizations. Level four adding more control to your visualizations. Level five, we’ll have a look at some of the lesser used of visualizations. Level six will be all about mapping.

Level seven will be measuring performance by using KPI gauges and cards. And then level eight will be everything else which doesn’t fit neatly into those first seven levels. So, with this introduction in mind, and by the way, you can download this and any other files that I use in the resources which you’ve just passed, you’ve just passed the resource lecture and you’ve you can download all of the items that I use from there. Just go back to the previous lecture if you haven’t already, and downloaded these items. So now let’s start with part one. Level one and we’ll be importing data from Microsoft Excel and creating our first visualization.

2. 1b. Importing from Excel, and Creating our first visualization

Now in this video, what we’re going to do is create our first visualization. We are going to go through 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 visualization. So those are the three steps you can use getting the data, transforming it, and then then creating your visualization. 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. It doesn’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, an earlier article that you could have downloaded. So if you haven’t downloaded them, please do so. So I’m going to click on Excel and I’m going to go to my resources and it’s this going to open it and you’ll see there’s a connection and a new window appears. It says, okay, you got this spreadsheet, but there are a lot of tables. And if you’ve got any named regions set up, they will also be shown.

So the table that I’m going to use is the HPI admins. What this table is, it’s the house price index for certain regions in England. So what the average selling price is 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, but we’ll only see one of them in this preview. 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 Lord. You could edit it. That will take us into another dialog box, which we’ll have a look at much later. Because at the moment I want to concentrate on actually creating visualizations 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 is 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 recognize the sigma as the summing. So what this sigma is telling you is that the computer thinks that this is a number which can be totaled together. Similarly, if it doesn’t have the signal sign, it thinks it isn’t.

So it might be a date or it might be some text. So what I’m going to do is create a visualization 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 the six different regions. And I’m going to add 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 setting 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 if there’s nothing that power bi is about individualization, it’s creating good visualizations, being well presented. And we’ll have a look at how to format this table in the next video.

3. 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. The 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 the 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 visualizations 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 lit 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 visualizations. By the end of this visualization section you’ll be able to create so many different visualizations 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 looks fairly much like an Excel table in that we’ve got scroll bars, we can scroll both 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 if I wanted to see everything that happened on the 1 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 roles. And you can see at the bottom what’s happened.

There are 1584 rows but I’m only 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 is between dates. So I’m going to go from the first of the first 90 96 to the last day of 90 97.

So that would be 1231 nine, seven. 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 photo. I could also clear all filters and it will show me all of the data. Now, I just want to emphasize that selecting a photo 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 data sets.

So any visualizations when we create them, will be created on the full data set and not just what we are seeing. When it comes to text, we’ve got a similar sort of filters. Equal, doesn’t equal, start with, end with, contains and doesn’t contain. And with numbers, we’ve got things like is greater than, greater than, 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 particular date. So now we’ve got all of the things that happened in January the first together and then February the first 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, 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 visualizations, you can always go to your data pane, your data button here, and just have a look. You can filter down for a particular, 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 visualization.

4. 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 in very shortly at how to genuinely increase the font size. But suppose you actually had a visualization which was this small, how can you best work with it? Well, you see there’s a little icon down near the bottom. Now, what this is called is forecast mod. Now to activate it, you to click on the visualization.

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 visualizations temporarily from what you are looking at. It removes everything, all of the distractions and allows you to concentrate on this one visualization. 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 visualizations pane and you see that we’ve got this particular visualization selected and we have the table selected over here. Now we can change the visualization 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 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 chart, a donut chart, there are 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 visualizations.

And it just makes sense to learn the mechanics of how you can do things in Power bi by having one constant visualization and then looking to see how this is changed in other visualizations. So in this video we had a look at the focus mode and we had a look at how to change one visualization 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 visualization, you must select it first. If I click away nothing selected and I click on a visualization, it thinks you’re trying to create a new one. We’re 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 visualization, and then I can change it to whatever I want.

5. Why do I need a Work email address? And how can I get one, if I don’t have it?

Now to sign in to the Power bi service, which you will 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. 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 us 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 is a work email address, there is a question why? Well, suppose that my email address was Philip At, maybe I’ll just use my website FILECATS coordinate UK the idea of power Bi, especially at the pro level, is to share.

And so I would be able to export anything that 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 at falcats john at falcats margaret at falcats and the idea is that I could produce a report or dashboard and share it with others in my organization. So what is my organization? It is all of the people that are users who shared this domain. So at falcats cog UK in this case, so that’s why it doesn’t want you to use, for instance, Gmail. account. Outlook. com, they don’t show a particular organization that you are in, they show instead an organization 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 other people 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 e three or e five account. Now you can get a free 60 day trial. So if I click on any of these so this is office 365 e three. You can see that it normally costs about $20 per user. There is also an Office 365 E Five which costs a bit more at some $35 per user. But notice there is a difference making form decisions with data analytics.

So if I have a look at the differences between E three and E five so the both all give you Microsoft Office, they all give you email and address. This is the email bit that we need. It’s a work email address. But if I go down fairly, you can see here that we have advanced analytics. And one of these two. The E Five gives you power. Bi pro. Now Power Bi pro this is the professional version of Power Bi. It major differences when you want to share reports. So you can either sign up for an Office Three six five e Three or an E Five.

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 anything 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 address 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 E Three or Office 365 E Five 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 organization name. However, I think this is 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 at something .

And this will then be your email address that you can use as a work email address. Now if you like more information about this subject then I have attached as a resource to this lecture a video by a people called Gynacube which go through these three different options use a work email address, sign up for free trial of Office or use as your 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.

6. 10b. Saving visualization to the Desktop and to the Power BI service

So now let’s save our first visualization. We can go to file Save or Save as and just name it my first visualization. Now it is a power bi file PBI x. 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 item. So I went to the exit right at the bottom previously. So now I’ve opened up my visualization 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 the 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 entered when I was setting this up a couple of videos ago. 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 proving not a robot, so typing your telephone number and it goes through a send you a number, which you then have to put in. So I’ll just skip this on this video. Now I can click sign up. So create your account by creating the same information that we entered when we set all of this up. I still 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 then.

And click start. And just to let you know, passwords must be between eight and 16 characters, combine upper, lowercase letters, numbers and a symbol. So a very complex password 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 we’re going to subscriptions, you can see that I have a license 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 you seem to be 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 my first visualization to Power Bi. And there we can now click on it, we can open Power Bi. So this opens up a new web account, a new Web page. I’ll sign in and there is my first visualization online. Now, admittedly, it’s not the brilliant visualization, but what we have done, we have saved our visualization to our desktop using Power Bi and we have also published it to the Web. So we have now got all our account set up and we can now publish further and better visualizations 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 visualizations.

7. Practice Activity Number 1 – The Solution

Hi, how well did you do in this Practice activity? So this was just a gentle introduction to Power Bi, but we’ve done all of the main elements. We’ve got some data, we’ve loaded it into Power Bi. We have created a visualization. We saved it on our local hard drive and published it onto the Power Bi service. So first of all, have you got the data? Now if you haven’t then then have a look on the right hand side under section one you should see Resources. Currently it’s on video number five. It might be slightly different by the time you watch this, but there’s a resources section here. Now if you click on the lecture you’ll see that there are the resources there, or if you click on this drop down box there are the resources there. So we’ve got power bi data and source data. F now they’re both the same. The only difference is that Power Bi data, the spreadsheets are in alphabetical order, whereas in Source Data F, they’re grouped together in various categories.

So whichever one you feel more comfortable with. So I’m going to download Power Bi data so it’s just ten megabytes and you see how quickly that downloaded, at least on my machine. Then we open up the Power Bi service. So I’ve got a little power bi icon here. If you haven’t then maybe search for Power Bi desktop. So whichever way you do it, just open up the Power Bi desktop so it initializes a blank model to start with. So I don’t want to do anything here unless I wash to. I could just click on Get Data to get the spreadsheet data loaded. Alternatively if I close this and go to home Get Data. So either way if you get the Excel data so if I could have a click on Excel or if I clicked on more it would give me this dialog box and again I can select Excel.

So lots of different ways to do the same task. So click connect. I’m going to go to my downloads, which is where it’s just been downloaded, and there is my Power Bi data that I’ve just downloaded. So I’m going to open that and it’s going to give me the options of okay, what spreadsheet do you want? And the spreadsheet we’re going to be using is the Practice Activity CDs. Now there is another spreadsheet called CDs, but want to use the Practice Activity CDs. You can see what happens when I click on the Practice Activity CDs spreadsheet.

We get in the headers number, file name, open file extension and so forth. We don’t get that when I click on the CDs because there are these free header rows in the CD spreadsheet which I’ve got rid of for this practice activity. So in part two of this course, we’ll be finding out how to manipulate the data. So for instance, remove these free header odds so that they don’t interfere with our data. But for now we’re going to have a look at these Practice Activity CDs. So I need to check this box and click Load and you’ll see on the right hand side these fields are populated. So we have the source data, Practice Activity CDs and we have the individual fields that you can see.

So the next part of the activity, and by the way, if you can’t see it for some reason, if it looks like that, then there is a drop link there that just opens up this particular source. That’s quite useful when you’ve got lots of data sources and you just want to focus on one of them, you can collapse all the others down to just the name of the data source and then just expand the one that you’re looking at. So the next part of this practice activity is to click on the fields file extension and size.

So just with two clicks you can see that we have got an analysis of all of the file extensions or three of them and the number of bytes. So we’ll be looking at how to format this, make this better, make it actually readable in the next few sections. So now we just save this page to our local hard drive. So go file save as choose where you want to save it. I’m just going to save it here to this download section and I’m going to call it Practice Activity One and notice it’s being saved as a Power Bi file which has an extension of pixy. Power BIXX, just meaning it’s a standard file and not a template. So I save that and then the next question is how do we save it onto the Power Bi service? Well, in the previous video we had a look at how to sign up to the Power Bi service.

So if you haven’t done that, have a look at the previous video and just get your account signed up with an email and a password. So having done that, I go file publish to Power Bi. So first of all I need the email address that I signed up with a couple of videos ago. Click sign in. So again this is the link if you haven’t got this account, so sign in and then we need the password and click sign in. So here is our published to Power Bi. I’m going to upload it to my workspace select and now you can see it is being published. So we have success. And if I click on this link, we will open it up in Power Bi and it’s asking me to reenter my password. So there we go.

I had to have two goals of that. I will save the password and there is my Practice Activity One uploaded for other people to be able to use if they have got access to this. So in this practice activity we opened up Power Bi, we load the data in, we created a visualization and we saved it to our local hard drive while if we go exit so save the changes, we can open it up again. Click on Power Bi desktop, however you’ve managed to open it, whether it’s through the start menu or whether it’s pinned. And here we can see practice activity one. And here is our visualization again. So I hope you enjoyed that practice activity. Hope you’re getting a good feel of how to open and create your first visualization and save your files and publish them onto the Power Bi service. And in the next section, we will be looking at how to format this visualization.