Power Query parameters | Add flexibility to your queries | Excel Off The Grid

when we use power query to transform our data each of the steps that we undertake is recorded in the applied steps window now what we might not see is in the background power query is recording all of those steps as functions in a language known as m but as we undertake those steps various values that we might use are hard-coded into that M code so for example if we filter for values greater than 50 that number of 50 is hard coded into the code but what if we want to change that 50 to an 80 or 100 does that mean we have to come back into Power query every time to change that value well that can become very time consuming and very annoying very quickly thankfully there's a better method that we can use and this involves using power query parameters so in this video that's what we're going to look at how can we make our queries more Dynamic and robust by using power query parameters so if you're ready let's get started there are multiple ways to create parameters and I think the most useful method for Excel users is if those parameters are contained in cell values then when we refresh our query it uses those values inside those cells to determine the output of our query now technically these are not official power query parameters parameters inside power query have a special purpose and a specific way of being created and we'll look at that later in this video however for our purposes cell values operate a lot like parameters we'll use them a lot like parameters so let's just call them parameter shall we okay here's the data that we've got it's some sales data that contains the date the customer the product who it's sold by and the value and this is contained inside an Excel table called sales data the first thing we want to do is to get this data into Excel so with a cell selected from the table I'll go to data and then select from table slash range and now the power query editor opens up and we can see our data in the screen there I haven't got any data types applied at the moment so I'm going to select all my columns go to transform and detect data type so my date column has come through as a date time I'm just going to change that to a date I'll replace that current step my customer product and sold by are all text and then I have a value which is a whole number in this scenario this is a quantity sold and we can only have whole numbers so I'm perfectly happy with that right now let's make some transformations to our data so the date column selected I'm going to go to date change this to a month and select end of month I'm then going to filter this so that it only includes values from the 31st of January and I'll click ok now as you'll see in the formula bar that date has been hard coded into our M code and this is one of the areas that we're going to make dynamic in a few moments time now actually for this scenario I don't need that date column so I'll select that day column and then I'll right click and select remove the other filter I'm going to apply is on the sold by column I want to select only David and I'll come and click ok so you'll notice here that we have filtered by everything where the value equals David and once again that has been hard coded into the M code let's say I don't need this column either so I'll select the David column right click and then remove okay that's enough transformations and they go to home close and load close and load two and I want this as a table on a new worksheet and then I'll click ok that table has now loaded into Excel and this table contains only the sales for David in January 2019. but what if we want the values for Sally for March 2019 well we don't want to go back into our query and make those changes so instead let's create two tables so up here in G2 I type the word date for the 31st of March 2019 and then down here in cell G5 I'll call it sold by let's enter the name Sally with this selected I'll press Ctrl T to turn this into a table I'm going to call this table date I'll do the same with the other parameter my table does have headers so I'll select ok and this table I'm going to call sold by next we want to load both of these tables into Power query so I'll select on the date table go to data and then click from table slash range now in our sales data if we come back and have a look at our column here so date has a date data type so we want to make sure that we apply the same data type to our parameter sometimes this isn't required but making sure it's the same is a good way to avoid any potential errors so from there I'll change our date into a date data type I'll then right click on the value and go to drill down you'll notice that we now just have a single value of the 31st of March 2019. here on the previous step we had a table view but once we drill down we no longer have a table instead we just have a list with a single value in it right let's close and load this parameter into Excel so home close and load closing load two now the thing is we don't need this value to appear anywhere on our worksheet its only purpose is to filter the M code so I'm going to click create connection only and then click ok you'll notice in the queries and connections pane that we now have a query called date that is of a date data type and there's a connection only query Leicester the same for our sold by table so I'll select a cell in there go to data from table slash range in our original query the sold by column was a text value so let's change this to text I'm going to right click drill down and we now have an individual value of Sally I'm going to go home close and load close and load two and again we're going to load that as a connection only if you're more familiar with power query you'll know that we don't have to load each of these queries individually but just for this use case it's easy to do it in this order so in our queries and connections we now have our sales data the date and the sold by with the date and sell by both being connection only okay I'm going to go back into Power query and now let's apply these parameters so over here we have our filtered row step and we can see the date so instead of the date we want to change that for our date parameter you can see that it's been recognized there inside the intellisense the second filter we have is on the step called filtered rows one and that's where it's filtered to David let's take out David and let's replace that with the sold by parameter again you can see that appears inside the intellisense let's just close and load that all back into Excel and in Excel we now have the value for Sally for March 2019. well if we changed it again to say John for the 28th of February 2019. so data refresh all there we go we now have parameters that we can use to easily filter the data that we have inside power query there you go how easy was that all we needed was a few extra queries and then to change those hard-coded values into the names of our queries now as I said earlier technically these are not parameters because parameters have a special meaning inside power query so what we've seen so far is kind of unofficial parameters because they are linked to cell values now let's head over into Power query and look at the proper parameter functionality so we're now back in power query from the home ribbon you can see this item called manage parameters so click that button you can see that we have this dialog box called manage parameters from here we can create parameters so select new I'm just going to call this sold by two I can enter a description if I want to I can also State what kind of data type it is so let's refer to this as text I can then have what kinds of values do I want so I can enter a list of values manually or if I have a query that already has a list in it I can use that query for now I'm just going to list the values and for our sold by column there were five values that we could have so Sally John David mark and Lucy from that I can select the default value I'll select Sally and I can also select what my current value is so I'll select Sally in there as well then I'll click OK and that will create the parameter so now we have this other icon on the left sold by two and you can see that we now have a drop down with those options that we entered in that list if I click manage parameter it brings us back to this window where we can manage the parameter that we've just created or we can also create a new parameter just click cancel on that for now so now if we're working inside power query we can use this sold by two parameter instead of a cell parameter so I'll come to my filtered rows one and my new parameter is called sold by two that's now been applied I can see that that has been filtered by my sold by two selection so if I change that back to Sally and go back to my sales data that now updates to only include the items that have been sold by Sally why would we use this other type of parameter well sometimes they're necessary for example if we're creating certain types of custom functions or if we're using incremental refresh inside power bi so parameters created in this way are recognized by power query as having special features whilst parameters created inside cells don't have those special features hopefully you'll agree that using parameters in this way is really useful because it helps us to create queries that can be more Dynamic we might use it to filter down to a cost center or a division we might even use them to change our source files now I would say that if we are using that method we should be aware that we might fulfill of the formula firewall error which hopefully I'll cover in a future video but we can get around that issue we just need to know how to do it so thank you for watching hopefully you found this useful if you did don't forget to subscribe and I'll catch you next time

Channel: Excel Off The Grid

Views: 42,468

Keywords:

Id: 28T0XzGNZyM

Length: 12min 28sec (748 seconds)

Published: Thu Sep 29 2022