Power Query - Faster & Easier Parameters

power query parameters you might know them as variables or input or conditions but whatever you call them they allow the user to customize the execution of a program to meet their specific needs creating parameters and power query can be performed different ways in this video I want to show you a popular and easy way to create and use parameters this happens to be the first way I was taught to use them then I'm going to show you a better way that will not only make utilizing parameters easier but also speed up query execution time by several orders of magnitude as a bonus I'll show you how you can pull parameters from multiple locations without the need for creating multiple parameter queries we'll begin with this blue table that has my data and then this green table that I've set up as what will be my parameter table notice that I've set this table as a proper Excel table and I've named it parameters also notice the headings option and selection that will become important later I'm going to bring the green table in as a query but then store it at as a connection only query where it doesn't actually produce any output then I'll bring the Blue Table in as data to be processed based on the settings of the green table so let's first bring in our query table so I'll go to data from table range the selection column holds all of the users choices now since this column has a mixture of dates and words I can't set a data type for it so I'll have to leave it as the any data type now this table isn't actually going to produce output it's going to be used to feed the user selection to the next query so I'm going to go to the lower part of close and load and say close and load to and then tell it I want to create just a connection now we'll go to the Blue Table the data and bring that into Power query so again from table range there are a few columns in here that I don't need so before I set my data types I'll get rid of those columns so I'll go to choose columns and I don't need the invoice number the state or the cost now I'll click a column heading perform a control a to select all the columns in the table and then I'll go to transform and say detect data type now as with a lot of things in Excel and power query it's easier to build something in a static format just to make sure things are working properly and then turn it into a dynamic format since we need to filter by a date range product a region and a supplier we'll go ahead and just invoke a temporary static filter so we'll go to date date filter between and then we'll just pick a date range so I'll do all dates occurring in 2023 now we'll go to product and we'll pick just one product it does doesn't matter which one same thing for region I'll pick one region and I'll pick one supplier if we look at the M code for when we filtered by date you can see that it's filtering each row but only keeping the rows where the date is greater than or equal to this date and less than or equal to this date same thing for the next set of filtered steps we can filter where product equals baseballs region equals Central Etc what we want to do is we want to replace say this static entry for baseballs and have it point to the parameters query and go get the selection for the product row now to point to any location within this table we have to point to the query name parameters the column name selection and then the row number so in this case golf balls is on the third row now there is a catch power query doesn't begin counting with one so it's not rows 1 2 3 4 5 instead power query Begins Counting at zero so the rows in this case are 0 1 2 3 4 so be mindful of that when you're referencing a specific row number so we want the parameters table selection column second row so going back to the sales info I'm going to remove the statically declared baseballs and instead tell to go to parameters and then in square brackets the name of the column selection and then in curly braces the row number and remember 012 so the second row if I hit check we're now filtering for golf balls we originally had baseballs statically defined but the parameters table is now telling us to use what the the user selected which was golf balls now let's do the same thing for the region the user selected Northwest we had statically declared Central let's expand the M code now because I don't want to type this all over again I'm going to highlight the dynamic entry for product copy that then I'll replace the statically defined Central and all I have to do is change it from row two to row three let's do the same thing while we're here for supplier we'll replace the statically declared athletes dream and instead of pointing to row two we'll point to row four hit check and now you can see golf balls Northwest Sportsman's then just like the parameters table has it set up now that works for these text based entries but what about these dates if I go back to filtered rows which was the step that filtered for a specific date range if I try that same trick by replacing the static entry of a date equal to X and I replace that with a pointer to the parameters table selection column but I'll start with the zero row and then go all the way through the first row when I hit check we get an error CU if you look back at the parameters table this column which is just set up as any doesn't understand that those first two entries are dates so when we try to feed those into the date filter there's a data type mismatch so what we need to do is convert the entry in the parameters table into a date so to do that we're going to need to wrap this parameters pointer in a date. from function and I'll have to do the same thing for the end range parameter now when we hit check the Filter Works let's go to home and load this out into an actual table and now we have our filtered output let's change the parameters we'll do January 1st 2023 through December 31st 2023 we'll switch to basketballs Central Specialty Sports right click the output refresh now although this works one of the problems of bringing the parameter table in the way we did is that the specific categories have to stay on the exact same rows if product and region were to switch their positions the query would break because the query is going to those very exact row numbers to pull those bit bits of data let's look at a way to bring the query in so that we don't have to worry about the row positions this is going to make the query more robust and a lot easier to build so starting from the beginning we have no queries let's go to our parameter table and go to data from table range we'll bring in the parameter table if you have a change type step go ahead and delete that step what we're going to do is we're going to go up to transform and we're going to transpose this table so instead of having an option column and a selection column and then the options are hardcoded to those rows we're going to transpose this now the options are in columns instead of rows we'll use the first rows ah header and set our data types this is going to give us two major advantages one is we don't have to worry about the whole which row is an entry on because everything is on row zero and so now instead of saying go to parameter selection two for the product we can say go to parameters product and the number will always be zero so the only thing we now have have to think about is just the name of the field and so we can say things like product region supplier and everything is just row zero so we'll go ahead and set this up as a connection only query now we'll bring in the data and like before I'll remove the columns I don't want set the data types and now I'll do my static filtering I'll go to date between I'll set up a static filter January 1st 2023 December 31st 23 we can see in the formula bar that the start and end dates have been hardcoded back in parameters we want to replace those hardcoded values with the entry from start date and end date both of which are on row zero so I'll select the first hardcoded value change that to parameters then in square bracket start date and then because everything's on row zero I just put zero in the curly bracet now let's do the same thing for the hard-coded end date so this will also point to parameters square bracket pets and date and then curly braces zero so always zero let's hit check and now we filtered for all 2023 entries I'll rename this step to filter dates now let's create a static entry for product so we'll filter for baseballs just statically then we'll go into the M code and replace the static entry with parameters bracket product and then curly brace zero and now we filtered for basketballs we'll do the same thing for region filter by region replace the hardcoded selection and then finally the same thing for suppliers pick one selection statically and now replace it dynamically let's rename these so we can keep better track of them so filtered product filtered region filtered supplier and just for a final touch on the output I'll sort the results in as sending order by date let's go home load this out to a table I'm going to put the results right here so I'll change my arguments right click the table refresh and I have a whole new table let's go back out of the query so looking back at the M code we can see for date we filter by the start date and the end date for product we filter by product region by region supplier by supplier the code is a lot easier to read we did not have to convert the start and end dates using the date. from function because if we look back at the parameters query those data types have already been set so powerquery understands them as dates they don't have to be converted now aside from easier to understand mcode and easier to write Dynamic queries because everything's on row Zer and we can reference everything by name something that I didn't clue you in on when we were using the previous version when we had to actually go in to the dates and convert them using the date. from function is that that conversion slows the query car down tremendously looking back at the earlier version where we had to use the date do from function to convert what was on row zero into a date or convert what was on Row one into a date that conversion process is slowing us down likewise if you point to a row that has text you may find situations where you have to use the text Dot from function to convert whatever's in that cell to text and if that were a number you'd have to use the number. from function to convert that back back into a number the data set I'm working with has 10,000 rows in it refreshing this query takes over a minute because of all of that conversion that has to take place converting text to dates or text to numbers or in some cases text to text this is all slowing things down by having the parameters table where each parameters in a separate column I can actually assign the data types now they're not all combined in a single column that has to be data types generically since they have native data types when I pull them in as Dynamic parameters I can just reference them so dates go to date text goes to text numbers would also go to numbers because there's no conversion taking place this query when executed took two seconds if I were to change the parameters right click refresh 2 seconds so we went from over a minute to refresh because of all the conversions to 2 seconds so the two main benefits of having your parameters stored on a row as opposed to a column are ease of implementation and speed because of the lack of data type conversions now here's a bonus trick for creating parameter tables what if you don't want all of your users choices to be in a single table what if you want to spread the choices across the form so I might want to have the product Dro down here above product but then skip skip the state column and have region here and supplier here same thing for my dates I want to put them in a different location I go to the table right click refresh and I've got my new output now if this was all the data you had you would have to bring this date range in as a parameter query you'd have to bring product in as a second parameter query and then region and supplier in as a third parameter query now to keep from having to create multiple parameter queries what I've done if I scroll over is I have the parameter table over here the way I had designed it originally as a single table this way I only have to bring in one parameter table and then do the transposition and data typing turning it into columns instead of rows but what I'm doing is I'm linking each of these cells to these respective cells so let me zoom out a little bit so this cell D3 is feeding the first row of the table D4 is feeding the second row of the table C7 is feeding the third row of the table E7 is feeding the fourth row and F7 is feeding the fifth row so the user makes their choices in one location and those get fed to a centralized table which is then brought in as the parameters table for power query so it is this green table that's feeding parameters not the cells that the user is actually making their choices this green table could be hidden placed on a different sheet that sheet could be hidden and this will give you the freedom to design this user interface any way you wish thanks for watching and remember at bcti the learning never stops

Channel: BCTI

Views: 18,169

Keywords: Excel, Parameter Table, Parameters, Power BI, Power Query

Id: -ibpZWeqzTI

Length: 13min 38sec (818 seconds)

Published: Thu Oct 26 2023