5 Best Practices in Power Query

in the last few years I have done a lot of work in power query trying to clean up very messy data sets and even performing some very complicated data Transformations and I found myself going back to these five practices over and over again to make my queries more robust what are these five practices you'll find out let's start [Music] best practice number one please name the steps that you create in power query now take a look at this query I've done some work in the query from The Source up until the change type here but just by reading the names of the steps there is no way that I can understand that what transformation has the data gone through because the names are generic like custom one custom two custom three so on and so forth now if you were to name these steps just by looking at the steps you'll be able to understand by and large that what has been the transformation that you have done in the query in case you would like to understand how do you name a step and you haven't named the steps before it's very easy all that you do is click on the step use the F2 key you can also right click and choose rename and then write whatever name that you would like to provide and this step is going to be renamed now I'm going to show you another query which is where I have renamed the steps and you will see the difference in understanding the query here is another query which is the same query as before the only difference is that here the steps are renamed and I have taken the effort and the time to rename every single step take a look at this we start with the source data keep all the files with the year label convert the Excel files to table combine the data of the Excel files add the year column Group by the year and the customer and change the type now even if I'm not really taking a look at the code becomes really professional and really nice and easy to take a look at the steps and understand by and large what was the transformation that went through in my query that's awesome best practice number two a lot of times you're going to be performing some very complicated data Transformations and it's not really possible for you to write the name of the work that you have done within the name of the step it's hard maybe it's long so what do you do you have the ability to write comments or notes so take a look at this step right here I am converting the Excel files to table so in the previous step I had the content and the content was containing binaries or Excel files and I've converted these Excel files into tables in The Next Step not only that but I went inside of the data column and just kept the first row of the data which is not mentioned anywhere in the step I have just written Excel files or table so how do I tell the reader to keep that thing in mind that only the first sheet of the Excel file is going to be processed so what I can do is I can right click right here and go over to properties once I go over to properties I can write whatever description right here once you have written the description you can click on OK and you're going to see that this step appears with a bit of an exclamation mark or like a a Mark that tells you that there is something special going on with that step and if you just hover the mouse on top of it you're good to get to see the comment that you have written not only that even if you went to the advanced editors go in the view tab enter the advanced Editor to take a look at the M code you're going to see that that particular step has been prefixed with a comment and that comment is that note that we wrote now this particular thing you could also do it within the advanced editor so if I maybe just come right here do the comment right here and write any comment right here you're going to see that the next step is also going to get that sign and it's going to have that thing so you can see that the next step also gets like a little sign a Mark that it has a comment and if you're performing very complicated data Transformations I would highly recommend that please write the notes for the user to understand that what are the Transformations that you're applying and are there any considerations for the user to keep in mind when they are updating the data or taking a look at the query or whatever that might be the users are going to be so happy with your work best practice number three notice and omit the red red is danger what do I mean by that now if you were to take a look at the query and try to find out the weak points of the query where possibly the query can break you've got to take a look at the M code if we don't understand it that's totally all right take a look at the M core and find what is red in the M code I'll show you what it is if you go over to the advanced editor right here and if I just zoom in right here you're going to see there are a lot of places that are red so this is red this is that means that's a hard-coded input all of this is red all of this is red and all of this is red red are typically inputs that have been hard coded within the formulas these are generally column names or any parameters or any values that are declaring now these values are susceptible to break the query in case anything in the source data changes so try to minimize the amount of red that you take a look at in the query let's just take an example as to how do you minimize the red so if I just maybe click on done right here you're going to see that the expanded data step is expanding over the previous step so if I just go over to the remove other column step you're going to see that I expand these three tables and hence I create this step which is expanded data if I go over to that step you're going to see that I actually have expanded all of these columns from the tables that were there in the previous step but the names of the columns are hard-coded now let's just say that I want to kind of not really hard code the names of the columns what can I do about it you're going to see that this is nothing but a list a list of the column names so date uh sales rep customer amount profit and the region They are packed within the list now what I can do is I can go if the names of the columns are the same I can go over to the previous step I can peek into the table and all the columns names are the same and what I can do is from any of these tables I can just fetch the names of the columns and plug that in within this step take a look I'm just going to go over it right here I'm gonna maybe delete all of this and I'm going to say something like table dot column names and I'm going to say hey the names of the columns are going to be picked from the previous step which is remove other columns right and from where do I have to pick up and remove other columns you're going to see a table the table contains a column called Data the data contains a column called sorry the data contains the first value as a table within the first table I want to pick up the names of the column headers that's what I want so I'm just going to go ahead and start to write something like hash remove all the columns that's the name of the previous step within this step I would have to find the column called data which contained the three tables and from the three tables I'm just trying to pick up the first table so that index is 0 because the counting and power query starts with zero now this is this should work I'm just going to check why is it not working table dot column names all right the bracket is missing so I can just close the bracket towards in the end and I can commit to this and that now reduces one possibility of the red signs in the query take a look at the query it doesn't really have those red signs anymore and those red hardcore inputs have been converted into a formula so take a look at these opportunities to automate the hard-coded values into the formula itself so that your query becomes more robust and does not break best practice number four parameterize and Stage the queries that you're creating what do I mean by that let's take a look so if I just go over and take a look at the queries that I'm working with in all the three queries I have the source step and every single Source step is leading up to a particular directory of the files which is where I'm fetching the data and you're going to see that I'm hitting the source three times why don't I create a parameter and then feed that into the step right here what I could do something like this so I can maybe first of all create a parameter so I'm going to go to the Home tab in the Home tab I have something like manage parameters and a new parameter and I'm going to create a parameter like a path parameter now that is where I will stick the path right here so I'm just gonna maybe say ABC right here I should have copied that first and I'm gonna go right here go to the source Tab and copy that path Ctrl C on that come to the path parameter and paste that path right here now the path contains where do you want to go and take a look at it now what I can do is I can stage my queries that means I only collect the data once and feed that into all the queries that I'm doing which is nothing but staging so what I can do is I can create a blank query so I can say something like a new query and I can say something like other sources and applying query and I'm going to use the function to get all the files of that folder so I'm going to say something like folder.files and in folder.files I can say something like path and press enter and this is going to be nothing but all data and now the old data query can be the source of all the three queries that we have it here now this is one type of staging the other type of staging could be more complex staging which is where you're trying to reference an in-between step of other queries or you're trying to reuse the work that you have done in one query to support other queries that you're trying to perform now I've done quite a few videos talking about such sophisticated techniques that how do you build sophisticated staging and get interim steps of other queries in power query I'm going to leave a link to a few videos and you can even learn about sophisticated staging in queries best practice number five it's not really a best practice but it's one of my preferred practice I do this all the time to help me write the queries faster you don't have a quick access toolbar in power bi but however you have the quick access toolbar in power query and you should definitely use that now up on the topic this is the quick access toolbar and I have two commands right here whatever you end up using in power query a lot you can place that on the quick access toolbar for me it's the advanced editor and the custom column and I've just placed those things on the quick access toolbar how do you do that for example I go to the view tab right click on the advanced editor and I say add to the quick access toolbar that has already been added so that's grayed out the second thing is a custom column right click and then add to the quick access toolbar now whatever you choose to add gets added up on the top and then you can use the alt and the numbers to trigger that particular thing I'll show you how so let's just say that I want to open up the advanced editor what do I do I use the ALT key and then on the first position is the advanced editor so if I just say alt 1 I'm going to open up the advanced editor that's nice and easy but if I want to make a new column how do I do that I'm just going to say alt 2 and that is going to trigger the custom column right here keep the commands that you use very often in power query and that is going to shave off a few seconds from your query creation process all right that's been eight five best practices in power query let me know what you think about this are there any preferred practices that you follow which have tremendously helped you in your query creation process I would love to know in the comments if you have any questions around this feel free to post a comment and I'd like to give a big shout out about my tax and my power query training courses in case you are a beginner and you're starting on your journey with power bi and you'd like to get on top of the fundamentals really well understand power query tax data modeling and then even solve harder problems even of your own data I'd highly recommend that you take a look at my courses it's going to be super awesome thanks so much for sticking on us out and I will catch you guys in the next one cheers bye now foreign [Music]

Channel: Goodly

Views: 41,329

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: D6ho5zOwZnA

Length: 11min 30sec (690 seconds)

Published: Fri Mar 24 2023