2: Importing and transforming data using Power Query Editor
2.1 Navigate the Power Query Editor
2: Importing and transforming data using Power Query Editor
2.1 Navigate the Power Query Editor - Video Tutorials & Practice Problems
Video duration:
8m
Play a video:
Video transcript
<v Narrator>In this sub lesson</v> we're going to get into the Power Query Editor. We're going to teach you how to navigate the Power Query Editor. So you gain some comfort with the environment and we're going to explore other key features. Okay. So I've got a Power BI desktop file open right now. So this is one of the ones you can find in the GitHub repository. What I'm going to do here first is we're going to get ourselves into the Power Query Editor. So up on the ribbon we can see the home tab and it is currently highlighted. And then over in the middle of the screen in the ribbon we've got this queries group right here. So within the queries group there is a command right above called transform data. If we click on this transform data this is going to get us into the Power Query Editor. So let's go ahead and click on that and we'll go ahead and let the Power Query Editor go ahead and open up, like so. Okay. So now that the Power Query Editor is open and I'm just gonna encourage you to kind of sit back and just watch this discussion here. Don't try and follow along with it with any of the files here. Cause I want to show you how to navigate around the Power Query Editor. So what we're going to do here first is we are actually going to go down to the product query. So I'm gonna highlight on this, cause we're going to use this query to walk through all the navigational pieces of the Power Query Editor. So first at the very top of the screen we are going to use and show you the ribbon. So at the very across, the top of the screen here this is the ribbon common in all the Microsoft365 products. So that is the ribbon, within the ribbon. there is going to be individual tabs that we're going to be clicking on quite a bit and going into get at some of our commands. So those are the tabs And then, within each one of these tabs. If I just go ahead and remove our highlighting there so far we will see that we have groupings of commands. So what we're going to do here is I'll just go ahead and highlight this group right here. So between the two gray bars here, we can see that we've got this group called query and a series of commands that exist within that group. So just to go ahead and quickly recap here, ribbon tab group, and commands. So those are really the things we're going to be dealing with a lot through our entire journey of Power BI. Okay. So now let's focus in on the areas that we're going to be interacting with a lot more as we go through the Power Query Editor. So on the far left hand side of the screen we have got our queries pane. So I'll just go ahead and highlight there. And we can see in here we've got a series of queries that we built up. Now, this file is the accumulation of all the steps you're going to go through in lesson number two here. So that's why you see a lot of queries here. So here's all the queries that we have listed. We can go ahead and open and close the queries pane if we see fit. So we can give ourselves some more real estate. So that opens and closes. And there's really nothing more to say about the queries pane. So let's now move to the right hand side of the screen here. Let's go ahead now and take a look at the query settings. So that is everything that is over here. So we'll see the query settings has a place where you can go ahead and rename the query if you want. And then I wanna draw your attention to a section within the query settings right here called the applied steps. Now this contains all of the steps that you're going to go through from the time that you connect your source data all the way to the point of where you're finished working with your query. And you're just about ready to deliver it over into the Power BI Data model. Okay. So the next area that we're going to cover is the formula bar. So if I go ahead and at the top of my screen here you can see I've got this thing right here where you will see formulas being entered as you're going through each one of your transformational steps. So what that looks like, cause if I just work my way through each of these applied steps I'll just go through a couple of them here. You're going to notice that the code in the formula bar changes. Now we will go through each one of these steps as we move through lesson number two, the code that is being generated up here is what is called the M language. And you don't necessarily have to go in and type any of this code in your own. You're going to find that the wizards and configuration screens are gonna allow you to generate all this stuff automatically. So if we just walk through each one of these applied steps you'll see the code changes in that formula bar. If you're not able to see the formula bar the way that we go ahead and turn that on is up here in the view tab. And there is a check box here called formula bar. So I can go ahead and turn that on and off, if I choose. My preference is to leave the formula bar on because I find this a really good way to learn the M language. I can actually watch what is being generated as I go through each one of the individual steps. So I personally find that a little handy if you do want and you're afraid of getting in and typing over some of this code or doing something wrong with it go and turn the form of the bar off if you want. But my general recommendation is to go ahead and just leave it on. Okay. So the next area that we're going to focus on is right here in the middle and this is what is referred to as the data preview pane. So as we move through each one of our steps in the applied steps, this is a picture what our data looks like at that point in time. So we can see here, our first step is sourced. So this is us making our initial connection into our source data. Then we navigate to a piece of that source data. So in a lot of our cases we will connect either Excel tables or sheets of data. So we're navigating into what we want to connect into in our sheets. And then we go through a series of transformational steps and what you'll notice here as we move through each one of these transformational steps the data in the data preview pane changes. So we can see what that looks like at that point in time. And when we get to the very last applied steps this is what the data is going to look like right before we go and deliver into our data model, if that's what we choose to do. The next area that we want to show you here is at the very bottom of the screen. And this is some metadata about the data set as it exists at that point in time as well. So we can see if I just zoom in here a little bit that we right now have 11 columns, oops 11 columns of information and 504 rows of data at that point of time, in the applied steps. So the other piece of information we have in here as well is the column profiling and in Power Query by default, call them profilings based on the top 1000 rows of data in your data set. And it's generally recommended to keep your column profiling at the first thousand rows. The reason we do this is that allows you to move through your transformational steps fairly quickly. And you can imagine if you had a very large dataset you wouldn't want to be profiling your data necessarily on the entire data set for every one of your transformational steps. That would take a long time to actually work your way through Power Query. You can use the sampling of the top thousand rows to go in and bring your data in, apply your transformation steps. And then right near the end, when you attempt to load in your data model, if there's any quality issues there'll be pointed out to you, and then you can go ahead and go back and get those items cleaned up. So we do recommend you leave that at the top 1000 rows but you do have the ability to go in and actually change this as you see fit. So I'm just going to zoom out here and if I want I can actually go in and click on this column profiling here and I can change my column profiling to the entire data set if I want it to. But like I say we're going to leave it at the top thousand rows. Cause that is generally good enough. Okay. So that is really all the navigation features that we want to show you. Now it's just about time to go ahead and roll our sleeves up and start working with some data sets in the Power Query Editor and going and working our way through all these individual steps as we do that work. So let's go ahead and do that.