If you don't already have all the software listed here, you will want to make sure that you have it in order to follow along with the course. If your it team is responsible for downloading software on your devices, take your laptop to them and tell them that you need all of these items. If it's your responsibility to add software, you can follow the following steps to add the necessary tools to download both the desktop application and power BI online. You'll want to open up your browser and go to power bi.microsoft.com..
This is your power BI landing page, right in the middle. You'll see a button that says get started for free. That's what you're looking for. This will take you to a window where you can download the necessary features on the left. You can download the desktop application, make sure that your version of Excel has a matching bit version with the version of power BI. You will be downloading. If you don't already know what your Excel bit version is, you could find out easily by simply opening up any Excel window, going to file choosing account and find.
The command called about Excel at the top. Here it should list and tell you exactly which bit version you're using. I'm using 64, but you might have 30. Just make sure that you download the appropriate version of power BI for whatever bit version you have on the right hand side, you can choose to sign up and install or enable your power BI online features. This is only necessary if you don't already have an office online account through your company. If you have an office online account through your company, it's likely that power BI is a feature that is already possible..
If not already enabled in this case, you'll just want to go up here and click on sign in. Once you click on sign in, it may ask you for your credentials, just go ahead and type in your login and password. And once you've done that, it will take you to the power BI online suite. Now that you've actually gone here, the power BI tile in your waffle should be enabled. So anytime you're in office online, you should be able to click on your waffle in the upper left hand corner and among all of your list of other.
So now we need to know what version of Excel we have and what ins might be necessary. If you have Excel 2016, no additional admins are necessary. Everything is already built in. However, if you have Excel 2013, you'll need to download and turn on the power query, add in. And if you have Excel 2010, you'll need to download and turn on power query and power pivot. You can do this fairly easily by opening up your browser and just Googling.
Power query, add in for Excel and putting your year in there as well. So here's my example for Excel 2013. And I'll see right here that it says, download Microsoft power query for Excel. Now this isn't an official Microsoft site. You'll want to make sure that you choose an official Microsoft site that says office.microsoft.com. I'll go ahead and click on it. I'll click on download now here..
Of course. I'm just going to make sure that I'm choosing the correct bit version for my version of Excel. Here's my matching version. So I'll click on that box and I'll go ahead and click next until it's download. Once it is downloaded, you will also need to open up Excel, go into your file tab, go down to options and add in the add in. So find your Atkins item here. And these power BI features are in a collection of add ins called calm.
Addins, which you will find from this managed drop-down menu at the bottom. Then you'll want to click on go. And at this time you can click on the box corresponding to the feature that you're turning on and click. Okay. Once you've done that, you should be all set. Please take a moment to pause the video and ensure that you have all of the above necessary items to follow along with the course. And then we'll get started..
Let's start off with an overview of what power BI does. The BI in power BI stands for business intelligence, business intelligence tools, analyze data to help businesses make better business decisions. Power BI is Microsoft's new business analytics. What's even cooler is that it's free. And it puts the work of analysis into the hands of the individual. For a long time, BI services were considered expensive and usually done by teams or outside services. Historically it took a lot of people and a lot of money to get it done..
Posts Related:
But power BI is a self-service business intelligence tool, meaning you or I or any
One individual has business intelligence power at our fingertips, the visuals in power BI or leagues beyond the visuals and Excel, even with all the new additions to Excel 2016 charting world plus nothing is more impressive than a dashboard. The hub at the heart of power BI is offering power. BI is also a competitor to Tableau who has held the majority market share in the BI world for a long time. Tableau is amazing and it also costs money..Power BI is free and easy to use for anyone, which is why it's getting Tablo a run for it. This course is designed to disperse topics into four days. Each day consists of a few hours of video and a couple hours of lab work mixed in. You can expect to spend five to six hours in each day's agenda. Of course it is totally up to you how you choose to direct your learning path. That is the beauty of these online videos. If you follow the straight path, you will flow through the following topics. In order day. One is all about getting data ready for well presented analysis..
The wonderful thing about power BI is queering feature is that you only need to set this up once with each dataset. You won't have to do this again. And again, every time you get new data like in Excel or other modern spreadsheet programs, once the data is cleaned and organized, you'll be ready to do some simple analysis. Using power BI is visual design canvas. We'll step into the basics of report design and learn how to build customize and slice visuals. Day two is when we get into data modeling, if you've ever chatted long enough with.
A database person, you've probably heard that the world would be a better place. If we all used SQL, instead of having thousands of Excel files roaming around, not agreeing with each other, the data modeling feature in power BI and Excel is a CQL engine for each program. In many ways, it's a fix for this long standing problem. It also fixes all kinds of longstanding annoyances, like allowing pivot tables to have more than one table as a data. And you can build your own custom functions called measures. At the end of day two, you will be building beautiful dashboards and sharing them with your organization in day three..
Many of the simple querying strategies that we learned in day one are looked at through the lens of real-world complications. When these complications arise, what tools can we use to address them? There is an unpivot feature you will definitely want to know about and being able to create a custom calendar once and use it anywhere is going to come very much in handy. We will also troubleshoot the very common problem in all data model technologies of complicated relationships, something that we will see and fix firsthand..
They, four is all about preparing for real world usage. There are strategies for handling the most common measure problems out there today, including comparing a selected portion to an entire population. This is a trouble spot in a lot of technologies, easily tackled with a couple of Dax functions when preparing dashboards for real-world usage by consumers. We've got some great tips and tricks, including designing for mobile consumption, creating custom filters and slicers and creating security measures to allow certain teams of people access to certain.
Information and not other information. We have a whole new way. We're going to be collecting data, analyzing data and presenting findings. The future of Excel is power BI let's get started. Welcome to day one in the real world, you are probably pretty familiar with your own datasets, but in these training environments, you're usually not as familiar with the data. So I would like to start off by getting us a little bit more familiar with the data we are using the Contoso dataset. It's a popular training dataset created by Microsoft a long while back. And it's used in a lot of trainings..
Contoso is a fictional retail company that sells their products worldwide. By the end of day one, you will have transformed a local data web data, and a little bit of curiosity into stunning visual analysis and business insights. You'll be able to make the reports displayed here. Our goals for day one are to analyze our company's wages. We're going to perform multiple analyses to discover some insights about that. And we're going to visualize those analyses to make it consumable by other people in order to achieve that we're going to need to fetch data from the many different places where we keep it many different Excel files..
We have some in an, in a text file. We have some information online. And we're going to need to clean up that data so that we can analyze it. We're going to need clean it up, normalize it, organize it, combine it, all that stuff. Once it's in an analyzable format, we're going to use power BI to discover insights about that data. We're going to learn to use a few different, very common and important visual types in power BI and organize our insights into a convenient report. Stay tuned and we'll get started. If you haven't already heard of a query, you want to be a little bit.
Familiar with what they are because we're going to be using them a lot. A query is a request for data.
So when you're submitting a query, you want to ask the following questions, what data do you want to retrieve? Where does that data live? How do you want to organize it or normalize it or combine it? Now this is power query specialty. And where do we want to put it? The feature in power BI that allows for querying is commonly referred to as power query power query was actually the name of the tool before Excel 2016,.When Excel 2016 came around, it is now referred to as get in transform. And then of course in power, BI is called something else as well. So this gets a little bit confusing. And so we will want to clear this up right from the get go here. And we'll talk about where power query is in each one of your. In power BI itself, power query is built into the tool, but it's not called power query. It's called get data. And it's also called edit queries..
So get data is the feature that's actually going to retrieve the data in the first place. And then if later on, you decided that you want to change something about that query. You can always go to edit queries and that will allow you to modify existing queries. If you're using Excel 2016, those tools have been rebranded. So they're no longer referred to as power query or later, we'll talk about power pivot as well. They've been rebranded. And now in Excel, 2016 on the data tab of the ribbon on the left hand side, you'll have a command group called get in transform. This is going to be the feature that allows you to submit new queries and also.
Allows you to modify existing queries. Now, if you're using Excel 2013 or 2010, you're going to need to download the power query, add in. And once you add in that add in, it's going to show up as an extra tab of the ribbon called power query. However, you're getting to it in this course, just remember that the feature commonly referred to as power query has actually been rebranded, but you're really looking for the word query. Anytime you see the word query, that's going to allow us to edit and use power..
For our first exercise. What we're going to be doing is taking all this data that we have in all these different places. And we're going to be combining them inside of Excel. Later on, we'll do an exercise where we do the exact same thing, but we combine all the data inside a power BI. It really doesn't matter which way you're doing it. It's going to work the same because remember it's the exact same engine. It's just rebranded by Microsoft. So the real question is what do you want your end result to look like? If you want your end result to be populated in Excel, then you're going to want to issue your queries all from Excel..
However, if you want to be able to do some nice visualizations in power BI, then you probably want to issue all of your queries from power BI. In our first scenario here, we're going to issue all of our queries from Excel. Now we have our data in many different places. So we have some data coming from our north America division. We have some data coming from our Europe division. We have some data coming from our Asia division. Each bit of data is coming in as an Excel file. We want to combine that all inside of one Excel file, and then we're going to need to fetch some extra data..
That's not even in Excel. We're going to need to fetch some data. That's hosted on an HTML page. We're also going to need to grab some data that is hosted inside of a text file. We're going to need to combine this all inside of this same table and power query is going to be our magic tool to accomplish. So, this is the part that you'll want to follow along with. We're going to be querying some data and we're going to be placing it inside Excel. I have Excel 2016 open. And so this tutorial is going to be about how to fetch data from all those different.
Places and put it inside of Excel 2016. I'm going to start from the data tab of the ribbon, because that is where I have my power query feature. Also known as get in transform. I'm going to issue a new query from scratch, and I'm going to query from a file specifically from a workbook because I have three workbooks to query in this exercise. If you have downloaded the work files, all of those workbooks should be inside of a folder called student files. If we click on student files, you will see a file called geography, Asia..
We're going to start with geography, Asia, and then we'll do geography Europe, and then we'll do geography north America. But we'll just start with geography Asia first. So I'm going to click on that and then click on import. So this is our first preview into power query. This is the navigator window that allows me to select different types of data that I have stored in different ways. Inside of that particular file. You'll see that I have something called table one. Now, inside of table one, I have a lot of data in here. What this means is that somebody actually went in here and grabbed a set.
Of data that they put in here, and then they chose to format it as a table. It's possible to do this inside of Excel. Now, normal data doesn't actually need to be formatted inside of a table. If we click on geography, Asia, you'll see that there's no table reference here whatsoever. This is just the loose data as it's stored inside of the Excel cells in that Excel file, I'm going to choose geography Asia. And the only reason is that I like that the name is already there. And if I click on the name right there, I'm sure that the name is going to be pulled in along with this query. So it's going to be a little bit more obvious to me what data I'm pulling in..
So I'm going to choose geography Asia, and then I'm going to go and click on either load or edit for our first few examples. We're going to click on load and that's just going to load it straight into Excel later on. We're going to choose edit, which allows us to do all of the magic power querying features inside of power query. But for now, we're just going to choose to load it straight into the Excel cells. Let's just make a couple observations. First of all on the right-hand side, I have this new panel called workbook queries. And up here, I can see that I have one query so far that is geography Asia..
There are 115 rows loaded there, and there are seven errors. If I hover over this, I can get a nice little preview of what the query itself actually looks like, which looks really similar to the data as it's populated there at the spreadsheet. Right? If I look here at the columns, I'll see that in the column called actual, there are a couple of cells here that say error, error, error, error, error. That's probably where my seven errors over here are coming from. Um, often your errors are no big deal, but sometimes they are a big deal. And so you want to investigate them later in this circumstance..
I know that they're no big deal, so I'm not going to worry too much about it. Notice that you have a couple of other options here in your peek preview. You'll be able to come over here and see the different columns that you've imported. The last refresh there, the load settings that you have right now, we're choosing to load it straight to a worksheet. And then the data source for this, you can also choose to view it in the worksheet, or you can edit that query. Or if you click on the little ellipsis here, you have a bunch more options. All of these options that we've just covered are also viewable by simply right, clicking on the query. And then you get a menu of options that show the exact..
All right, we're going to start off by just importing those three queries. So we have two more queries to import geography, Europe and geography, north America. I'm not going to create a video for this. I'm going to let you do this. So this is your chance to get an exercise in the new tools. You're going to pause the video here. You're going to go to your data tab. You're going to start a new query from a file from a workbook. And then in your student files, first, you're going to import geography Europe, and then you're going to import geography north America..
Once you've actually chosen the file, you'll click on import and then in your navigator window, make sure to choose the sheet on the left hand side and click on load. Go ahead and do that. And come on back. I'm going to do a couple of handy things here. These are not necessary to making your queries work in the real world, but they're just ways that I like to see my data. If I have queries populated in three different sheets, I really like for my sheets to be named appropriately..
So I'm going to rename my sheets, geo Asia, geo Europe, and geo north America. The other thing that I'm going to do right now is I'm going to save and save often. It used to be that we had to remind ourselves constantly to save files. Lest we lose our hard earned work. Um, not so much these days because we do have recovery features. However, we're doing so much cool stuff. I don't even want to lose one step. So I'm going to make sure to go back to the 1990s and save and save often..
So I'm going to click on my save button and I'm going to save this file as geography. I'm going to make sure to store it inside my student files folder, and let's keep going. So now that I've set this up, so that visually, it looks kind of nice. I want to actually start to clean up the data so that it looks kind of nice. Let's make some observations about our data really quick. In our geography Asia query, we have 115 rows loaded and it looks like we have geography, key geography type continent ID. It looks like whoa, I have a totally blank row there..
That's probably not good. That's gonna mess up the number of rows that I've loaded. Right? I really should only have 113 rows loaded. If I have these two blank rows here. In addition, I'm going to look at this column right here and say, Hey, that's supposed to be state and region. Hmm. There should probably be separated into separate columns. Now, why would I know this? Well, it has to do with some really common normalizing strategies that we want to employ inside of our. If we use these normalizing strategies, we're going to find it's a whole lot easier to analyze our data..
Let's talk about some common normalizing concepts that we want to employ in order to make our data beautiful data. We're going to start by making sure that we reduce our data down to the smallest meaningful value. This means that in that column where I have states and regions, I'm probably going to want to separate that into two different columns. Let's take a look at some of our other strategies here, removing unnecessary data. Eventually we're going to have a lot of data in here and the more data we get, the more work my computer has to do to keep up with all of it. So if I can remove the data that I don't need, my program.
Is going to be a lot happier. We're also going to handle some errors. Different kinds of errors should be handled differently, but they should all be addressed before. Moving on. You want to make sure that none of your errors in there are actually errors that are going to get in the way of the analysis that you want to do. Later. We also want to make sure that we have compliant field names. We don't want any field names with weird characters. We also want to make sure to standardize our data. We also want to standardize our data types. This means that each column of data should have the appropriately applied data.
Type, meaning dates should have a date. Datatype text should have a tech stated. And numbers should have a number data type. When we start doing some interesting data modeling down the line, we'll want to make sure that we have created key or ID fields for every single table. All of these normalizing strategies can be realized very easily with power query. Now, remember the whole point of using power query for this is that it's going to memorize all of our normalizing strategies as we complete them..
Meaning in the future, if we get a new set of geography, Asia data or geography, north America, data power query is going to have memorized exactly what it needs to do with that data to normalize. It let's get started before we get started. Normalizing. I really want to make one point really clear. You really don't want to normalize any of your data before you bring it into power query. That's the magic of power query and power query is going to memorize it..
So if you're used to doing a lot of these normalizing strategies in Excel, try and encourage yourself not to do those before you pop it into power query and get power query to memorize it for you. That way you won't have to do any of this by hand in the future. That's the idea. Okay, let's go ahead and get started. The first thing we need to do is combine each one of these tables together. And the reason that we need to do that is that this is exactly the same data. It's just being submitted to us from different regions. So we have a table called geography, Asia. It has geography, geography..
Uh, city name, actual state and region status locations. Employees. If I go to each one of these other tables, I'll see that the exact same data is populated here. If it's the exact same data, it can absolutely belong on the exact same table. And if we put it in the exact same table, that means we can throw it into a pivot very nicely, very easily, and start to do some very easy analysis on this data. So that's the first thing that we're going to want to do is combine all of these queries together in order to create a query from an existing query, I can right click and choose reference. What that means is that it's going to use the results from the existing.
Query and issue a new query using the results from geography, Asia, meaning I don't have to start geography Asia all the way over from scratch. Right? I can just use the results from geography Asia, but the safety metric is that it's actually, uh, everything I'm going to do next is stored in its own query, separate from geography Asia. So I'm going to right click on geography, Asia and choose reference. That's going to create a separate query for me here and now we are in the query editor. Welcome..
All right. In my query editor window, I'm actually going to expand my query editor window so that we can see everything we got. The query editor window is so cool. First of all, on the right hand side, you'll see any query settings that you've already applied, including the queries name. Now, I don't want this query to be named geography Asia too. I actually want this to be called all regions because eventually what I'm going to do is combine the data from all those other tables into this one table called all regions..
All right. The next thing I want to do is take all this data that was the result of geography Asia, and actually take the data from geography, Europe, and geography, north America, and pretty much add it to the bottom of the list. Just making one very large list, the process by which you add one queries data to the bottom of an existing query is called upending queries. And that's exactly what we're going to be doing. So in the query editor, on the home tab of the ribbon, on the right-hand side, I'm going to look for the button called a pen queries. That's going to allow me to add Europe and then later north America onto the bottom of this list..
All right, I'm going to click on a pen queries there, and now I have the option to append queries. All right. So I already have geography Asia data here. I'm going to choose geography Europe. Now I actually have more than two tables. I want to append together. I have the existing one, geography Asia plus geography, Europe plus geography north America. So I'm going to click on this little button that says three or more tables, and I'm going to choose. Let's see, Europe is right there, north America..
I'm going to click on and click on add. And now all of those three are going to be upended together, clicking on. Okay. As I apply each one of these normalizing strategies, you'll see on the right hand side, there's an area called applied steps and I can actually see each step as I apply it. If I make some kind of a mistake and do something crazy and suddenly it looks all weird. I can remove that step by clicking on that little X right there. I'm not going to do that right now because I like the way that this resulted..