Create A Parameter Table For Your Power Queries

in this video we're going to take a look at how we can add parameters to our power queries so I've got a query here and it's connected to a CSV file and it's pulling in all the data so it's pretty small file there's only 16 rows of data and if we go into the power query editor so I'm just going to edit this query we can see that it's pretty simple query and it's only got the default steps so the first step here is the source step and we're connecting to our source data and you can see up here in the formula bar that we're connecting to this folder and this file here now this value is hard-coded into our query so this query is always going to be pulling in that exact file every time you refresh it but a common scenario is that you'll probably want to change the file that is pulling in so when 2017 oh two dot CSV file is created so February's file you're going to want to pull in that data instead and to do that you can create a parameter so I'm just gonna copy that folder path there and if we go into the home tab and manage parameters and new parameter then that's gonna allow us to create a parameter so I'm just gonna call this file path and that's gonna be a text-based parameter so I'm gonna pick text there and I'm gonna set the current value to that folder path and file and then if we press ok and then we go to our query pane here we can see this new parameter that was created file path and we can see the value that I've got there now if I go into my data query and go up to the source step and replace that hard-coded value there I'm gonna get rid of those quotes as well and if I just type in a new parameter and press Enter and then close and load that now this query is connected with a parameter to the same file and now if I want to change that file so when 2017 o2 comes in I can just come here and edit this parameter then if we come in here and change the value so instead of 2017 o1 you change that to two and close and load now our parameter is another folder name and file path and if we right click on our query table and refresh it we can see that we get our February data in here now so power queries native parameter functionality is pretty useful but it's also kind of a pain to always open up the query editor and change your parameter so what might be a better solution is to create a table of parameters so here I've got a table and if I look in my design tab name that parameter table and it's got two columns so it's got a label column so I've just labeled this as folder and then the folder value is here in a column called value and then I've got my file name label and the file name value here in the value column so what we're gonna do is hook up this query to our parameter table instead and that way when we want to update our parameters we just need to go into this table here and change the values and then we can refresh our query so to do all that we're gonna create a new query so we're gonna go up to the data tab and get data and from other sources and then we're gonna create a blank query so the first thing we're gonna do is name our new queries and this is gonna be the function name which we use to call values from our parameter table with so I'm going to name this F and parameter and press Enter and now we can go up to the advanced editor in the home tab and I'm gonna get rid of this default stuff here and I've got already got this prepared and my text editor so I'm just gonna copy and paste that and what this is is a query function so it's going to take two arguments a table name and a parameter label and with that we're going to connect to our table so this table name here is using the argument table name and then we're gonna look up in the parameter column for our parameter label and return the value in the value column so we can click on done here and now we're ready to use our function query so if we head over to our data query and go back to the source step again and this time we're gonna replace this file path parameter so we can delete that and I've also got what we're gonna put in here prepared so let's just grab that copy and paste it in and what we're doing here is we're calling the parameter table and looking for the folder items and that's going to return our folder path and then we're gonna concatenate it with a backslash to our filename from our parameter table so if we press ENTER we can see that we're back to being connected to our January data and if we close and load this we get our January data and that's because in this parameter table here I've got 20 1701 but we can change that maybe let's try 20 1703 and if we select our table right-click and refresh we get our March data so that's how we can set up parameters with power query we can either use the native functionality and create single parameters but then you have to go into the query editor and update your parameter so it's kind of a pain and the other option is we can create a parameter table so we can have a whole list of parameters down here and then we can look up them in our queries using this parameter column here and return the item in our value column so that's it for this video see you guys later

Channel: How To Excel

Views: 100,174

Keywords: Microsoft Excel, Excel, Microsoft, Tutorials, Power Query, Parameters, Get and Transform

Id: sdR2BI2e5Y8

Length: 6min 58sec (418 seconds)

Published: Sun Nov 04 2018