Transform your messy business data. If you do this step right, the rest of your Power BI journey becomes easy. It's easier to create your relationships, write your DAX measures, and create beautiful visualizations..
But if you mess up this step, you just make things much harder for yourself in Power BI. But don't worry, we will show you everything you need to know about Power Query in just about 20 minutes. I'm Avi Singh, Microsoft MVP and Power BI Pro, and my goal is to.
Make tech easy for the non techie. So make sure to subscribe and click that bell. Here's a list of chapters in this video. You can also jump to a chapter if you like. I would recommend watching this whole video first, then come.
Back and follow along step by step using the download files. This Power Query tutorial is complete in itself, but we would also point you to resources if you want to continue your journey beyond. All the resource links, including the one to download files, are in the video in the corner and in the.
Now, let's get started with our tutorial. Here we are in the Power BI desktop and we're going to start with the Get Data button. Now you have to understand that Power BI is like a machine or an engine..
And what does an engine need? It needs fuel. And that fuel, in this case, is your data the messier the better. All right, let's get started..
So if you click on the Get Data button, you're going to see the common data sources listed at the top. But the magical button is the More button, and I would like you to click that and you can see here that Power BI allows you to connect to a ton of data sources..
There's Excel, there's Access, there's SQL Server, there's SharePoint, there's One Drive, there's Azure and other cloud databases and so on. In fact, Power BI has the longest list of data connectors than any other BI tool in the market..
Now we'll talk about connecting to other data sources, but for this tutorial, let's start by selecting the Excel workbook and clicking Connect. Now here we're going to point to our Excel file Adventureworks database..
- Incluir NOVOS Usuários - Planilha Cadastro de Membros e Controle de DÃzimos.
- Tutorial Sync Notion with Google Calendar Using Make
- Tutorial Telegram Bot Automations with Make
By the way, you can download all the practice files including the - ID Card Make
Data and the Power BI files at the link shown here. Now what Power BI does is once you point it to any data source, whether that be Excel or SharePoint or something else, and then it examines that data source and it shows you the.Tables or the data sets that it finds inside. And it lets you select the ones you want to import into your Power BI model. And you can click on any of these entities to examine them or take a closer look at them..
So here you can see that these are the Excel sheets. This Excel file also has Excel tables and those are shown separately here. They are shown with the table names and you can also notice that they have a slightly different icon..
Now this may be a personal preference, but I do like to work with and connect to Excel tables. So here what I would like you to do is to just select each one of these by clicking on the check box next to them. So we're going to select the Calendar table, the customer.
Table, the Product Sales and Territories table and right now we're going to click load. Now I'm just going to point out the Transform Data button that is our window to the kitchen of Power BI. We're not going to quite go there right now..
I'm going to click load, but be assured that the kitchen of Power BI, the query editor, is running in the background and we are going to take a look at it in just a few moments. So what it's doing right now it is is it's copying from the data source, it's copying the data over here..
And now your model has a copy of that data set and you can see that here on the data tab over here you also have these other elements. Here you have the table view right here. Let's click on that..
And that lets us examine the tables. So here we are, This is the calendar table. I can click on the Customer table, look at Product sales or territory stable. Now there's also a model view or a relationship view here..
Now, relationship is of course one of the key concepts in Power BI, but we'll be covering that later. When I talk to you about more resources, I want to mention two things before we move forward. The first thing is Power BI is amazing at data compression and.
That's how you can work with data sets, which could be
Millions of rows, in fact hundreds of millions of rows and beyond. Now of course, when you're working with larger data sets, you do have to follow best practices and we'll point you to.Additional resources on that at the end of the video. The next thing that I want to mention is that even though we connected to an Excel workbook, but I want you to know that even if connecting to other kind of data sources isn't that different Now, whether you're connecting to a SharePoint file.
Or SQL Server or something else, the first prompt or the first question is going to be different. In case of SQL Server, it'll ask you to point you to the server name. In case of SharePoint, it's going to ask you the site URL,.
But once you give it the right information, it does the same thing as we saw it doing with the Excel workbook. It examines the data sets, shows you the tables of the data set it found inside, and then you can click and select the ones you choose..
From that point on, the experience in Power BI and the query editor is exactly the same. Now, so far we have brought in really clean data into Power BI, and how often does that happen in real business? Well, never..
Real business data is always messy data, and that's where Power BI really shines. So we're going to look at a messy file. Now, at first glance you would probably look at this and say Abby, this isn't messy..
In fact, this looks very clean. But that's one of the challenges that you have to learn and understand about Power BI. Now this one is a pretty clean file for a human, and this is what I call a human friendly format, but it's not a machine.
Friendly format and that's what makes it messy data for Power BI. Now notice kind of the odd things here. Again, they're odd for Power BI, they're cool for us. So there are these header rows which Power BI doesn't really.
Have a need for. It's only hungry for data, remember? And here we have these color formatting, which again Power BI doesn't really look into that. We have the grand total which is redundant..
Power BI would say that hey, I already have the monthly data, I can calculate the grand total from that. I don't really need this. I don't really need these subtotals either. And the fact that amongst are spread out over columns, that's.
Kind of awkward for Power BI. So again, from Power BI is perspective, this is messy data. But guess what? Power BI is extremely capable of handling with messy data like this or much much worse..
But let's step through this example. So we're going to go back to our Power BI file, go on the Home tab and click the Get Data button again. And this time I'm just going to select Excel Workbook from the sources and this time we're selecting our messy file, the.
Budget file. So I'm going to click Open, and again, whenever you're connecting to any data set, it examines what's inside, and in this case, it's showing me that one sheet it finds. So I'm going to select that by clicking the checkbox..
But this time we are ready to step into the magical realm of the kitchen of Power BI. So we're not going to click Load because we need to do some clean up. And that's where we're going to click the Transform Data button,.
Which is going to launch the Query Editor window. Now you might call it Transform Data Query Editor or Power Query. It's the same thing. Now again, this is one of the key components of Power BI and.
You can see this is a new window which is hovering over our main Power BI file. So think of it as again stepping into the kitchen. So if you need to clean shape and transform formed data, deal with messy data..
Stepping in the kitchen my friend. So here we are in the kitchen. I'm going to maximize this window. Now this is our first time in the window here, so I'm just going to familiarize you with some of the elements here..
Now notice I had promised you earlier that even when we had selected just to load the data, I promised that the kitchen was working in the background. And you can see that the tables that we had loaded earlier, they are here..
So, and you can click on the sales and you can see all of that is there. The data is there in the query editor. Now let's come back to the budget table and let's start by cleaning up this messy data..
Now Power BI. Often the query editor is going to take its best guess at what data transformations needs to happen. And mostly it does a great job. But in this case, well, we didn't quite need these steps..
So what you see over here is first of all the table name. Now this is important because this is the name when you load this file into Power BI, that is going to be the name of the table. So yeah, give it a good name..
But what's more important here are these applied steps. Now here, first thing I would like you to do is delete some of the steps which we don't actually need. So the change type step, you're going to hover over the red X here and just click that to delete that step..
And we're going to do the same thing with the next promoted headers step. All right, now we're ready to get started. So the first thing we notice in the file if you go back to the excel where these header rows that we need to remove..
And that's the best part about the query editor that any data cleanup data transformation you need to do. That's usually done by clicking on one of the buttons in the ribbon. And I love the ribbon interface..
Of course, working in Office, Excel, PowerPoint, we're all very familiar with the ribbon interface and that makes using the query editor really, really easy. So to remove these rows, which we don't need, well, guess what? There is a button for that right there in the ribbon..
So I'm going to click Remove Rows and Remove Top Rows, and it asks me the number of rows I want to remove and the answer is 3 in this case by looking at our file. And I'm going to click OK next. But before I do that, I want you to be looking really carefully.
At the display here and also the applied steps here. All right, Are you ready? OK, let's click OK and you notice that two things happened. First of all, the display changed. You can see that the rows that we didn't need, they have indeed.
Been removed. But more important than that, you can notice on the side here that that step was recorded over here. And we're going to come back and talk a lot more about that because that is one of the most amazing and incredible features.
Of the query editor. Let's keep going. And next I notice that our column headers that we need are now in row one and the column headers right now says column one 2-3..
