2.6 Introduce the Power Query Editor - Video Tutorials & Practice Problems

<v Instructor>Now that you've seen examples</v> of how to connect into the vast array of data sources that are available in Excel is now time to shift attention to the different methods that are available to users, to transform data using the Power Query Editor. So far we've just connected in the data sources but we have not actually done a transformation that data. So we've taken care of the E side of the ETL, but no transmissions have yet occurred nor have we loaded data to any target locations aside from showing you how to load the foreign exchange rates into the table, which we did in a previous sub lesson. It's generally a rare occasion where data that is made available to you, isn't a state you needed to be in for production quality reporting. If it is, count yourself lucky. If not, the Power Query Editor has a very rich set of features which allows users to perform transformations and cleansing activities with ease. You'll often hear this referred to as Data Shaping, which has become the more modern term for Extract, Transform, Load or ETL. So let's go in and take a look at the Power Query Editor and its components. So what I'm gonna do is make sure I've got the data tab highlighted like so. I am going to go over to Get Data, click on the drop down box and go to the Launch Power Query Editor. So the Power Query Editor will now open and I can see the queries that I have loaded in so far to my spreadsheet. So let's take this opportunity to introduce you to the pieces of the Power Query Editor which we're gonna to be spending quite a bit of time in over the next few sub lessons. So what I'm gonna to do is I'm just going to go over on the left hand side under inquiries and I'm just gonna choose Sales Territories. So just gonna click on it so that I get a number of other parts of the Power Query Editor opening up, so that we can go ahead and walk our way through. First off, let's go ahead and take a look at the ribbon. So along the top, we have got home tab which has a number of different transformational functions which we will go ahead and take a look at, shortly. We have the Transform tab, which once again has a number of different functions available to it and we'll review those. The Add Column tab has a lot of the similar functions that are available on the previous two tabs. The difference here is, with this particular tab these functions will do the transformation but add it as an extra column. So that is a very important thing to understand. So that is our ribbon. What we can then do is take a look at the Queries pane. So down the left-hand side here these are all the queries that we have built out so far. So some were generated automatically for us in earlier sub lessons when we did the folder import. And in these other ones down here these are the actual queries we had gone ahead and set up on our own. So we created the Sales Territory which was connected into a CSV file, sales which was indexed the Excel spreadsheet, products which came from the folder of information and then this USD to CAD foreign exchange came from our web data source. So those are the four queries that we have created so far. If we had created additional queries here such as parameters, functions, lists. That would all show up down in the Queries pane here on the left. At the very top of the screen. If I just go back up to Sales Territories, we will see that on my screen, I have a Formula Bar. So if you do not see the Formula Bar, the way to turn that on is to go to the view tab, and on the left, under the Layout group we will have the ability to click this Formula Bar check box, which turns it on and off. It is very useful to have the Formula Bar on because what is happening as we work our way through these sub lessons, we will be performing transformations. And the code that is generated for us automatically through these transformation steps, is all written in a language called M, which is automatically done for us, and a great way to learn any language is to actually watch how it is being used. I mean generated. So to start with, I like to encourage people to leave the Formula Bar on so that you can watch what is being generated and kind of learn from the code that is being worked on. Now as a general rule when you are getting into using Power Query Editor, you're not gonna really need to learn the M language that is underneath, but over time as you start bringing more and more complex data sources in and become a little bit more confident with the actual interface itself, you may wish to start venturing down the path of learning the M language and the code that is being generated underneath the covers here. And we'll summarize this at the very last sub lesson, when we go and show you how all the code was brought together. So that's the Formula Bar. Over on the far right-hand side we have our Queries settings pane. So we have the name of the query we're working with and in a couple of previous lessons we have changed those query names and a couple of spots and then further down below where all the different applied steps that we're applying to that query as we take a copy of the data from our source and eventually perform transformations on it to be delivered out to our target sources. So we will spend quite a bit of time showing you the applied steps and once again at the very end, after we've done quite a bit of work in that full sub lessons, we'll show you the aggregate of all this and how we can better manage the supply steps. And finally, in the middle, we have our Data Preview page. So if we wanna take a look at what the data looks like at the current transformation step which is in the applied steps right here. This is what the data would look like just before it gets delivered out to our target sources. Should were actually be target sources chosen. So this is the Data Preview pane. At the very bottom of the screen is some more metadata around the actual queries that are being brought in. So we can see here that I've got five columns in this query ,there's 10 rows. And it's letting me know the column profiling is being based on the top thousand rows. So a little bit more metadata about that. So now over here on the right-hand side you'll notice that we have the some preview information about when the information was last downloaded. So we can see that I last download this information 11:57 AM. So let's just do a little bit of extra metadata. So that is a overview of the Power Query Editor. And we're going to be working with it quite a few over the next few sub lessons. That brings an end to this sub lesson.