2.15 Better manage Get and Transform - Video Tutorials & Practice Problems

<v Instructor>So right off the hop</v> in the lesson introduction, I mentioned that there are some very interesting features inside a Power Query that really allow you to organize your Queries and document them effectively right within the tool. So let's go ahead and take a look at some of the things we can do to really improve the long-term maintainability of what we've built out. So I'm going to go back into my Power Creators, so I'm gonna go over to Get Data and say Launch Power Query Editor. I'll get back into the editor itself and we can see down the left-hand side that we have a number of Queries we have built up, and keep in mind so far we've only built seven different Queries up, but as you do work with Power Query Editor you can find yourself getting a lot of Queries. So what we might wanna do here, is we may wanna group up our Queries into different categories of Queries. So what I'm gonna do here is I'm just going to show you the ability to go ahead and group these and I'm gonna go ahead and right click in a blank space here over in the Queries pane and say New Group, and what New Group is gonna do is give me a brand new folder for putting my Queries in. So what I'm gonna do here is I'm gonna say, I'm gonna call this one Dimensions (keyboard clicking) like so and that's gonna create me a brand new group and I am going to go into here and say New Group and what I'm gonna do is I'm gonna call this Facts. So don't worry about what Facts and Dimensions are, just take these as two different folders that I can use to group my Queries into. So what I'm gonna do here is I'm gonna take my BudgetData and what I can do is I can right click on it and say Move to Group, Facts, so I can go ahead and do that, or I can do drag and drop so you take, let's go ahead and do the sales one next I'm gonna take sales and drop it up here, so now sales is in the Facts section. What I may wanna do now is take my Sales Territories products, this conversion rate and maybe my Product List in the Product Finished Goods. And let's go ahead and move that up into the Dimensions group like so, so we can see now I've got five of the Queries sitting here inside a Dimensions and for some reason it looks like the Sales one slipped out so let's go ahead and put that one back with the Facts. So I have five different Dimension Queries and two Facts here. I cannot get rid of this other Queries area here. This is the default group within Power Query Editor, so I can't get rid of that so I'm just gonna leave that one alone. Any brand new Queries I create will automatically go into here until I move them into a different area. So the salient point here is you have the ability to start grouping up your Queries and making them easier to go ahead and find and just promote some general organization. The next thing that we want to do is we wanna go in and make sure we've given our Queries good names. So in this case here I'm just gonna choose BudgetData. So I could go here and if I like the name of this Query website which actually don't mind, I'm gonna leave it alone but I can right click on the Query itself and go down to properties down below here, and I can take a look at the name and maybe I just want to put a space and just wanna call it Budget space Data, or maybe I wanna get rid of the word data completely and just call it Budget Sales or maybe Sales Budget, something along those lines so let's go like that call Sales Budget. And what we can do down here in description is add some commentary about maybe where we got this information from. So we can say, got this file from Bob in Finance, and this way if somebody else comes to pick up this Power Query from me and they wanna know where did all these Queries come from and who provided the source? I've got some information in here now I'd probably go and describe this a little bit better but I just wanna put enough in here to show you what the description is all about, So I click Okay and we're now gonna see that the Query name has changed to Sales Budget and if I hover over top of it the tool tip is saying got this file from Bob in Finance. And maybe since I was naming Queries like this, I wanna go through and rename this to Sales Actuals, like so (keyboard clicking) and then I can differentiate between the fact that I've got some Sales information and some Budget information. Okay, so in the spirit of naming our objects properly and giving them descriptions, we can go and do the same over in our Applied steps. So right here, if you recall back when we took a look at our Sales Query, we went and added in a brand new column and it took the unit price times order quantity and it gave it a default name called Inserted Multiplication which doesn't really mean a lot. What if we want to give that something a little bit more meaningful from a naming perspective, so we can right click on that, say properties and in here we can say you know, Calculated (keyboard clicking) what's this, Line Total something like that, this way when we take a look at this line, it makes a little bit more sense, and we can also put a description here so we can just say used the business rule from finance or something like that. So lots of finance touches inside of this file. And this really is a business rule, anybody could figure out but once again just showing you, you put some descriptions in here, go ahead and click Okay and now we can see that the Applied step has been renamed so makes more sense now, if I ever wanted to go find out where was the calculation for line total? It actually is a little bit more apparent and once again, if I highlight over top of this I can see that the commentary for that comes up as well, so we use the rule from finance. So once again I could go through and rename all of these steps. I could give them descriptions. There's not really a lot of point doing that right now across all these 'cause once I show you the technique for doing one you can go ahead and apply that to all the ones you want in whatever you have from a production capacity. What we wanna show you next here now is, let's go up to the top here in the View tab, so I click on the View tab and what I wanna show you here next is this thing called Query Dependencies, so if I go ahead and click on this, it will pull up a dependency tree and let's make this a little bit larger. I do realize I have to scroll and zoom in here a little bit. So let's click this and now what we see is a pictorial here, If I just make this a smidge bigger, this shows me how all of my data is being pulled from their sources and landing in the Power Query Editor and all the different steps the data's going through. So what I can see here is if I take a look at my Sales Actuals, for example, if I click on this one right here, I see that it turns green and I can see that it came originally from the folder here called Lesson 2 data files, l02s05 so keeping in mind that we got some of the product information, we're doing the merging together from here. So, all of that got built over here and then eventually was built into a Products List and then that Product List was merged in the Sales Actuals 'cause we want to get the product name, the sub category and category into it. And then the Actual Sales information came from this Excel spreadsheet here called sales.xlsx. So we can go through and see the way that everything was all built out, If I wanna take a look at the way the Sales Budget was built, I click on that and we can see that it came from the spreadsheet that was called pivot examples. And then even the commentary that we put into the Query itself shows up here as well, so I can see that we got this from Bob in Finance. So this is a really nice way to show you how all your data starts off and all the different steps it goes through from a step-by-step perspective so that you can get a bit of a picture as to how all these Queries depend on each other. So what we'll do next here, is just go ahead and close this off and the last thing I wanna show you is we mentioned that a while back in a couple of the sub lessons, that the language that underpins the Power Query Editor is a language called M, and at no point during the sub lessons we've gone through have we actually written any M code but the O has been generated through the steps that we went through when we did all the work in the Power Creator. So if we wanna take a look at the aggregate of all the steps that were used for a particular Query, we can do a couple different ways. So I can click on each one of these Applied steps. So I'm on the Sales Actuals Query and then click on Source and I take a look in here and it shows me where this information came from. So this is a piece of M code. Go down to the navigation and we see another piece of M code. Go down here and then M code, M code, M code, so each one of these steps forms its own line of code. If I wanna take a look at all together, I can click on this Advanced Editor right next to Query Dependencies and this will pull up all the M code that was used from start to finish, to take our data file for Sales Actuals and actually load it in and get it all cleansed to the final state. So as mentioned before, this is the M language. When you're starting off with using the Power Query Editor, I don't necessarily recommend you get in a play a lot with the M language, no there's probably not really a lot of need in most cases do so, but do go ahead and turn the formula bar, so you can watch the M code that is being generated as you're doing your work because it is a good way to learn the language. As you start progressing your skills in Power Query Editor, you will find a time where understanding the M language can actually save you a lot of time and make you a more efficient developer in the Power Query space. So let's go ahead and close that off, get that close out here and that pretty much concludes the things that I wanna show you for making your Power Query more maintainable and showing you some of the features and functions that make it easier for you to pick your files up months down the line or for you to hand them over to a different person and have them understand how you built all of your stuff up. All right, that brings an end to this sub lesson.