So that's coming. So make sure you watch this video till the end. I'm Avi Singh and even though now I'm a Microsoft MVP and a best selling Power BI author, have a YouTube channel with 10 million plus views and have helped thousands from all over the world through my learnpowerbi.com training program. But I started perhaps where you are right now in the data dungeon. That's where I was struggling to create reports, often feeling.
Overworked and underareciated. And those two, by the way, are guaranteed signs that you might be in the data dungeon. But fear not, because we have a plan to get you and your team out of there by walking you through these five steps. If you really want to get the most out of this tutorial, I would recommend watching it twice. First just watch the full tutorial and then come back and follow along with the download files. Now this video has chapters built in which you can use to.
Navigate or jump to a specific chapter if you need, especially if you're coming back to watch it again. And this is the link where you can download the practice files to follow along with the tutorial. You would also find the link in the corner and down in the description below. Just one more tip, change the quality settings to HD for the best video experience. Now, before we step into the demo and start walking you these five steps, it is important to understand the problems that we are solving for and these are the problems or challenges that.
Every modern organization is facing right now. Number one is that their data is spread across these multiple systems. And I don't care if you have a fancy warehouse or a big ERP tool, there's always data in Excel files and CSV files and SharePoint lists and vendor data and partner data and data in your emails and all of those places. And that's the first thing we're going to show you is how you can use Power BI to easily combine data from multiple sources. The next challenge that organizations are facing is that business data is messy data..
Of putting a boring report with tables and pivot tables? So this is the day and age to create modern, beautiful and interactive reports. And Power BI lets you do just that. We're going to see that in our demo as well. And lastly, the old way of sharing reports, which is through e-mail. It is clunky, it is 20th century and you don't need to do that anymore because Power BI gives you a very smooth, easy to use and secure online way of sharing your reports where the users can access them from any device, the computer, the tablet or their phone. Now this was important for you to understand because what you.
Also need to understand is imagine the impact that you can create for the team, the organization that you're working with, if you are able to solve these Big 5 data challenges for them, the impact you create for them and also, of course, for your own career or your business. So let's get started in solving these challenges at step number one, connecting to multiple data sources. So again, the challenge we're dealing with is data is spread across these systems, and let's see how Power BI can deal with that. Here we are in the Power BI desktop, ready to get started,.
And we're going to do that by clicking on Get Data. Now that's the portal for you to connect to a whole bunch of data sources, no matter where they are. Now, it's going to show you some common data sources, but if you click on more than you can see it that Power BI has a very long list of connectors. That means that pretty much no matter where your data lives, it's most likely that Power BI can connect to that. Even if you do not have a direct connector, you might be able to connect using an Odata feed or API, or as a last resort by.
Exporting data from your system. So I'm going to show you some examples of connecting your various sources and let's start off with the really simple Excel workbook. So I'm going to select Excel workbook and this is the file you should have if you got the download files as well. And I'm going to select the Adventureworks database and just click open. Now no matter which data source you're connecting to, once you're past the first few screens, it always just simply shows you the tables that it found. Inside that you can load into Power BI and you can see we have.
You can click on the table to see a preview of that on the side, on the side over here. Then we're going to simply select the first tables here, Calendar, Customer, Product, Sales and territories and click load. And what it's doing is it's going out to the data sources, fetching that data and importing it into Power BII should also mention that Power BI is extremely good at compressing data and that's why you can work with data sets which are millions of rows or even larger than that. So now these tables are here, we can see them show up on the data.
Posts Related:
- Amazing Excel dashboard for producers companies
- Mouse Basic PowerPoint Icon making Tutorial PowerPoint Show
- Excel 2016 Intermediate
Pane and if you were to navigate to the table view over here, you would be able to examine the actual data inside those tables.
But again, remember the problem that we are focusing on is how do we connect the data spread across these different systems. So as part of this demo, I want to show you two other connections. So on the left here we have data sitting in a SharePoint site. This is some sort of a monthly commentary that the team is adding for each month. Text commentary and on the right we have an Azure Sequel database. So let's next connect to our SharePoint data list..So I'm going to go to Get Data again and click More and this time I'm going to type in SharePoint in the search box here and select the SharePoint Online list. After selecting that, I'm going to click Connect. Now it asks me for the site URL. Now just one note here that it's not looking for the complete URL or the URL to the list itself. It just needs the first part of that. So just copy the URL to the site itself and then between these two options, one point O and two point O..
You know what, let's pick the latest two point O. And that also gives you a really nice option here, which is instead of retrieving all columns, SharePoint lists have a lot of system columns which we don't really need or use. So instead we're going to select the second option here, which is to retrieve the default view. With that all set, let's hit OK. And as I mentioned earlier, Power BI, no matter which data source you're connecting to, once you go through the initial screens, it simply shows you the tables that are available and.
You can pick and choose from that. So from here, the one we're looking for is a monthly commentary. And again, I can click on it and just kind of add a glance, see if it looks right, and it does. So I'm going to select that check box and click Load, and you're going to see that this data gets loaded and added to my table set over here. And of course, I can again go to the table view, click on that table and examine the data in itself. So already we have combined data from two different sources. Let's add one more or Azure SQL Database..
So for that one let's go back to get data, click more and here I'm going to type in Azure Azure SQL Database is right there. I'm going to select that and click connect. I'm just going to type in the server name and the rest of the options you can leave as default and just hit OK And again as before it goes examines the data set and it's comes back with the tables or the data sets it has found and you can expand that and select the tables that you want to see. As you can see as before I have access to all the tables. I can click around to see what's inside the table..
And again, this is just a test so I'm going to just select this table and load it into our model. But again, this is coming from our Azure SQL database. Now, once this is done, it's time to lift the hood and take a peek inside what's going on, which is this Get Data functionality really uses the Power Query engine inside that, and we're going to talk a lot more about Power Query in this tutorial. But the way to access Power Query is by clicking on the Transform button, Data button over here..
So if you go there and click Transform Data, notice that it opens a new window here. And this is what I call the kitchen of Power BI. This is where the magic happens. Now I'm just going to quickly refresh the preview because it's complaining about that. All right. So as you can see that all the tables that we had connected to are actually coming through the Power Query engine and I'm just going to rename this one table just so we can we can differentiate this..
So this one is coming from Azure SQL. This one is coming from. This is a SharePoint list. Now what I wanted to show you is a really cool feature inside the Power Query, which is the Query Dependencies view and you access that by clicking on this view icon over here and then clicking on this Query Dependencies view here. Now what this shows you is a visual view of all of your data sources and how they're being manipulated, changed, transformed or combined..
And of course what I want to point out here, as you can see how in the same data model, in the same data set, we have data coming from an Excel file, from a SharePoint file and from the Azure Sequel database. And not just that, it's not about just connecting and loading this data sets. We could even combine these two tables or do some kind of a look up or any other processing that we need to do across data coming from all the data sources. So we started with this problem of data being everywhere and now you can see how easily you can use Power BI to combine data.
From all different sources. We are ready for step #2, cleaning messy data.
And that's the truth my friends. Business data is messy data. Now we wish it was like this. And sometimes if you talk to IT or somebody who has built a fancy warehouse for you, this is what they would like you to believe. It's like, Oh yeah, just connect to this data set and it's all clean. But in reality, it never works this way. The reality is more like this. But that is where the good news lies, is that now you don't no.Longer have to be afraid of messy data. In fact, you can fall in love with messy data. Let's see how using Power BI. So we're back in Power BI and we are going to be connecting to a very messy file, the budget data. Now you might look at this and you would say Avi, what are you talking about? This is not messy. This is so clean and beautiful. Ma. Well what I would say is this is where humans and machines you have to understand how we are different. O this is what I call a human friendly data..
So it's formatted to really work for a human, but if you feed it to a machine, you'll probably not be very hay about it for some good reasons. So what Power BI would complain about is that you have these header rows which I don't really care about. I don't need that you have this coloring, which doesn't matter to me, I can't even read the coloring here. You have these grand totals which are redundant because I can calculate that using these months. You have these subtotals which are also redundant, and the fact that this data is spread across these columns, That's really.
Awkward for me. O you can see for how from a Power BI perspective this is indeed messy data. And of course, we run into lots and lots of scenarios when working with real business data and trying to fit it into Power BI. But guess what? We have talked about the kitchen of Power BI. Let's step back into that and let's see how we can clean, shape and transform any messy business data. Now before I move forward though, I'm just going to remove.
The Azure table from the model and the SharePoint list. So I just wanted to demonstrate that you can connect to multiple data sources. But let's go there again. Let's click Get Data and Excel Workbook and this time I'm going to select the other file which is part of your download files, the Budget file. And I'm going to click Open and this time from the Get Data dialog box I would select this. But instead of clicking Load, I'm going to click Transform Data which simply opens up the kitchen of Power BI again, which is the Power Query editor..
So I'm going to maximize the Power Query window and just familiarize you a little bit with this interface. So again, Power Query, think of it as stepping into the kitchen of Power BI. And just as kitchen has a lot of appliances to help you prepare your food, the Power Query interface has all of those gadgets and appliances up here in this rich, rich ribbon with lots of different tabs. And you're going to be seeing some of them in action as we try to clean this messy data. On the left here you see a list of queries of these are the.
Tables that have been loaded. And on the right here you see the name of the currently selected query. And this name is also how it's going to be loaded back into Power BI. So that name is important. And the lovely part, the applied steps. And I'm I'm going to ask you to keep a close eye on that and in fact we will start there. Now Power BI, when you connect to a data set, it tries to take its best guess on how it needs to be processed and often adds automatic steps..
Well, in this case those steps are not helpful and we don't really need them. First thing I would like you to do is to hover over this change type and just click X to delete that step and do the same for the next promoted headers step. Again, hover over that, click on the red X and it's deleted. And now we're ready to start the cleanup. And if you look back at that file, the first thing that we had noticed were these header rows that we do not need. And again, as I had said, the kitchen has lots of appliances, so right here we have the remove rows selection right here..
So I'm just going to go there, remove rows, remove top rows, select that, and in this case we need to remove 3 rows from the top and I'm going to hit OK, now you notice that that step gets recorded over here. Let's do a few more steps and I'll come back and talk more about this recording. Now the next thing we want is this first row, category, Subcategory. These are actually headers. And boy, I wish there was a button I could Click to promote these as headers. Well, guess what? Of course there is that is right here. Use first row as headers so I don't have to select that row..
But yeah, as long as you're in this interface, click Use first row as headers. And again, notice that that that step is recorded as promoted headers and Barbie I does like to add sometimes this automatic change type step. I'm not a fan of that, so I'm just going to hover over the red X and delete that too. The the promoted headers is still the same. And yeah, and you can see that in action over here. We got a bit more clean up here to do. You can see we have the subtotals here and the grand.
Total here and all I'm going to do is click on this column and say text filters and I'll say does not contain the word total. Now one word of warning that Power Query is case sensitive so if you do not use the right case like if you use the lower case T it's probably not going to work. So watch out for that, it is case sensitive so it does not contain total. And if I click again that step is recorded over here filtered.
Rows and you can see that that those rows have disappeared from here. Now if we if you remember that excel file it had that grand total which we didn't want. It's redundant, the machine doesn't need it. We're just going to go select that column and right click and say remove and again that step gets recorded and you can see that that column indeed has been removed. And now for the final act, and this is one of my favourites, because in the old world of the old Excel, I used to struggle so.
Much when needed to do something like this, which is again this data. The fact that it is spread across these columns is unnatural for Power BI. And if you leave it this way you're going to have a lot of challenges in the next steps when you start defining the Power BI formulas or DAX measures. So what we need to do is to take these columns and turn them into rows, an action that is called UNPIVOT. So yeah, I really struggle to do this, but let me show you how.
You can do that. In Power BI we're going to select the columns that need to be unpivoted and guess what? We just right click and say unpivot columns and bada Bing bada, boom. You can see all the data that was in columns is now neatly into rows. And we can change this to say this is the month. We can change this to say this is the budget amount. And finally, remember when it kept adding those automatic change type steps? Now I'm ready to specify the type..
So I'm going to click on the icon next to the column name here and I'm going to say this one is a whole number. This is a key or an ID. This one is actually a date, so I'm going to select date and this one is a fixed decimal number or currency and that step is recorded as well. Now I had said that I'm going to come back and talk about this steps being recorded here. Often when people see this, and in fact even when I first saw this, then coming from an Excel background, my reaction was, oh,.
This is like macros. But guys, there's a rule when you compare anything new in Power BI to something in that old world, you have to add the mandatory words but way more awesome. So yeah, you can think of it as like macros, but it is way more awesome. And let me tell you why. So in Excel macros, if you have used them, you know the challenges that recording them is easy, but what happens when you need to make a change?.
It becomes kind of a nightmare. Either you need to re record the whole macro or roll up your sleeves and dive into that really complicated code Then. Now you might have been noticing that Power BI in the back end is generating what's called the M code or the M language. But the best part, 99% of the time you don't have to worry about the code. And even when you need to change steps, changing a query is as easy and it's the same experience as recording a new query..
So you can go in here and you've already seen me deleting steps. If you want to change a step, you can click on the gear icon next to that and change the logic there. If you wanted to insert a step, you can go back to any previous step and just click on a button and it'll insert that step for you there. And of course you already seeing how it's awesome because you can really go step, step through these recorded steps and see exactly the transformations that was done. Which is another incredible thing about Power BI and Power.
Query specifically, which is that it is self documenting now, documenting how you created a report. It's really hard to do. It's really hard to keep the documentation updated. And now with Power BI, it's automatic, right? Because anybody knew who's maybe taking over the report. Or if you come back to this report in a month, you can come back and easily see exactly what was done. That makes it super easy to maintain Power BI models an update them. Let's finally hit close and apply and now we're going to step out of the kitchen of Power BI and Power BI is now going to.
Fetch that data and load that into our Power BI data model and you can see that table has been loaded. We can again navigate to the table view and click on the budget table to actually examine the data if you wanted. So we started with some human friendly but machine messy data and we have been able to clean it up and load it into Power BI. And all of these tables, no matter how many different data sources they're coming from, can be refreshed with just a single click of this button. And Power Query is going to replay all of the steps that we.
Have recorded for clean up and data transformation and refresh all of our data. And what's even better that you can also set up a fully automatic refresh and that means your users will always have access to the very latest information. So my friend, you no longer need to be afraid of messy data. In fact, it's time to fall in love with it. The next big problem that we often see is the formula spaghetti and I'm going to share some real examples. So I was working with a client and they gave me an Excel file.
That they wanted to convert to Power BI. And I clicked on one of those cells. And you know, there's this long formula there. And I'm like, OK, take a deep breath, we can do this. But then of course, as soon as I clicked on each of those cells, behavior formulas as well. And that's what we end up with, formulas and endless formulas. And there's one other nightmarish example. This is a Google Sheet. So yeah, Microsoft Excel, Google Sheet doesn't matter..
And this is a problem because something like this, anybody who has done it, and I did for many many years before I switched to Power BI, it's can be very hard to maintain, it can be error prone and very hard to update. And that's the challenge because business is always changing, reporting all always needs to change to adapt to new business needs and that the formula spaghetti makes it truly challenging. Let's see how Power BI redefines that using the magic of DAX formulas or DAX measures which you define once and use everywhere. So we're in our file now..
This one is the 03 starting file. So what I did is you as you know, chefs do on the cooking show, they kind of put it in the oven and then they take another one out which is somewhat ready. So I did do a little bit of work, I I updated some of the data types for the calendar table. I made sure all the relationships were set up accurately. Now relationships that is part of a a bigger concept of data modeling is a very core concept of Power BI that you need to.
Master. You need to understand how relationships work, how one to many, what is the default, what are the best practices, the data tables, the look up tables or as I like to call them, giraffes and hippos. There's a lot more there. We're not going to quite be able to dive into that, but you can just start with a 03 file and go from there. It's all set up for you and we're going to be focusing on simply the sales table right now for our DAX measures. So on our sales tables we have these relationships set up with.
The look up table as to who, what, where, when. And if you switch back to our report view now what you would realize is that you would say you would feel that, oh, I don't need to create DAX measures because I can just drag that column here and it gives me that math and I can do other things and I can add category to it and I can see it broken down. I can change it to a a table or a matrix and you can say that yeah, look, it is doing the calculation for me now I just want to warn you that if you do want to progress in your journey, you need to understand that what's happening even right.
Now is that Power BI is indeed defining a DAX measure for you. The only difference is that this is what's called an implicit measure. So Power BI is doing it for you without telling you about it. But if you really want to have control, if you really want to get good at Power BI, you want to make sure you you switch to what are called explicit measures, which is when you define them, right? So this is the the sales, this is the implicit measure. We're going to get into the sales table and I'm going to just hover over the table, just click on the three dots here and.
Select New Measure and this drops me in this formula bar here. And the first thing I'm going to change the measure name, let's call it Sales. And then the same thing which earlier was an implicit measure, I'm going to define it explicitly using the sum functions. So this one is pretty straightforward. So SUM and auto complete helps. So I want the Sales Amount column from the Sales table and I can just click that and it completes. I'm going to close the bracket and that is looking good..
I'm going to click the check box and you can see that measure is defined and you can see how it has a different icon which lets me know that this is a measure and this is special. Now let's add this to the. Oops, let's switch it back to a simple table. Oh, by the way, you probably already noticed. There are a few different ways you can change the visual. You can either select the visual and from the Home tab you can expand this panel and change that to a different visual..
