Power Query Parameters - Dynamically Change the Output of Your Queries Depending on Their Value

power query parameters give you the flexibility to dynamically change the output of your queries depending on their value as power query records the transformation steps it includes many hard-coded values within the m code for example if we filter the month column to select the month of january january will be a hard-coded value in the m code what if we want to change these values we could edit the query each time which would be very time consuming or we could create parameters parameters in power query are really easy let's see how we can do this [Music] here we have some sales data showing our sales rep name the products they've sold the sales value of those products and the date of sale we want to create a report where the parameters can be changed easily by our colleagues let's say we want the parameter to be the month that they select first we're going to create our query second we will create our parameter and third we're going to assign the parameter to our query this data is already in a table format and i've named it sales data under score 22. let's send this to power query power query has performed our source and change type steps on the right here as we want the month to be a parameter let's click on the date column to select it and in the transform tab click on the drop down next to date and go down to month and select name of month and we have our month names extracted let's rename date to month next still in the transform tab click on group by and click on advanced we want to group by month and sales rep let's call the new column name sales and we want the values to be summed and the column to be summed is sales value and click on ok we've now grouped our sales by month and sales rep next let's filter our month column on january and power query gives us the filtered row step this is the step that we will use to assign our parameter next let's create our parameter before we do that i just want to duplicate this query and call it example 2 as we will use it in our next example back to creating our parameter in the home tab click on manage parameters and click new parameter and we get this manage parameters dialog box let's name this parameter select month and if you want you can add a description i'll leave this blank for now the type is text as the names of our month column are text for suggested values i'm going to select list of values and type january press enter and type february and enter and type march for the default value you can select any of these values i'm going to select january and for current value i'll type january as well and click on ok and we have our parameter called select month and if we click on the drop down for current value we have the months we added as our parameters our third step is to assign the parameter to our sales data query that we created up here the value that we want to change based on the month we select lies here in the filtered rows step as this is where we filtered on the month of january to get the sales for that month and if we look over here in the formula bar january has been hard-coded as the text is in red if you would like to learn more on m code and the advanced editor then you should definitely check out this video here power query advanced with introduction to m code let's select january including the quotation marks and delete that as this is where we want to place our parameter start typing select month which is the name of our parameter and the intellisense brings it up select it let's test our parameter click on select month under queries here on the left let's change our month to february and go back to our sales data query and our report now shows our february data let's send this back to excel click close and load and we have our report in a new worksheet let's move this report to the same sheet as our source data if we look at our queries and connections pane on the right our sales data query loaded we will get back to example 2 and our select month parameter loaded as a connection only so parameters always load as connections only so if we double click on our select month parameter we are back in our query editor let's select january and close and load and hit refresh and our report updates to show our january data so this really enables flexibility to dynamically change the output of your queries depending on their value filter is not the only place that you can use parameters you can use parameters in any step that you would like to be dependent on a parameter let me show you what i mean in our sales data query let's add another step let's sort sales rep in ascending order but sometimes we want our sort to be variable maybe we want to sort on sales so here in the sorted row step is where we can assign another parameter let's quickly create that parameter before we look at how to create the sorted rows parameter almost 90 percent of my viewers are still not subscribed if you're getting value from this video i would really appreciate it if you could please hit the subscribe button as this will really help me hit my 2022 goal of attaining 1000 subscribers now let's get back to our video in manage parameters click new parameter let's name this parameter select sort we will leave our type as any let's make this a list of values and let's make our list to contain sales rep and sales let's make our default value sales rep and our current value sales rep also and click on ok and we have our select sort parameter now let's assign this to our sales data query in our sorted row step we have sales rep that's hard coded and this is the value we want to be dynamic let's select it and delete it and start typing select sort and the intellisense brings up our parameter currently we're sorted by sales rank let's test our select sort parameter and let's sort by cells and if we go back to our sales data query our cells are sorted from lowest to highest let's close and load to send this back to excel now let's say we want our power query parameter in an excel cell for example here i have month and product as parameters in excel cells and if i type january in month and desktop in product and hit refresh my query updates to show all desktop sales in the month of january we're going to use our example 2 query that we downloaded earlier this was just the duplicate of our sales data query now to set up the parameters in our excel cell we need to have each of our parameters in a single cell and convert this to a table so i've done that and named this table month and this one product next let's send our month table to power query and here in the query editor we have a month table next right click on january and click on draw down and our value january is returned what drilldown does is it enables us to select a value from a table cell so drill down refers power query to our last step which is change type and it tiles power query to go and grab the value in row 1 from the column min which is the column header of our table remember power query is 0 based so row one is position zero when using draw down the value can be saved as a new object or query and then used in further data manipulation in this case we will be using it as a parameter in our example 2 query now let's send this back to excel click close and load to and select only create connection and click ok let's perform the same steps for the product table send it to power query then right click on desktop and select draw down and let's send it back to excel as a connection only we're going to use our example 2 that we created earlier so let's double click on that our final query doesn't include product so let's click on grouped rows here in the applied steps and click on the gear icon to open the group by dialog box let's leave month as we still want this as our grouping and change sales rep to product we want everything else the same and click ok after the group drawer step we have our filtered rows which is filtered on january let's filter on product let's filter for desktop and over here in our formula bar in the filtered row step january and desktop have been hard-coded let's delete january and assign our month object that we just created in its place remember it's case sensitive so i'm going to select month from intellisense and delete desktop and assign our product object in its place also and hit enter let's send this to excel this is also fully dynamic so if we go back to our source data and add our march data here showing laptop cells and go back to example 2 let's change our month to march and our product to laptop and hit refresh and our report automatically updates and let's go to our sales data query that we originally created and double click on it to go back to our query editor and go to our select month parameter and change this to march and close and load and hit refresh our report updates with our sales for march i will be releasing a video on how to make your file path dynamic so please be sure to subscribe and hit the notification bell so you know when that video is released i will add it here also as soon as it's released in the meanwhile please check out this video here power query custom functions thank you for watching and see you in the next video [Music] you

Channel: Miss Microsoft

Views: 46,801

Keywords: power query advanced, power query parameters from cell, power query parameters power bi, power query parameters excel, excel, microsoft excel, power query, power query with input variables from excel sheet, parameter table, parameter query, how do i get criteria from excel sheet into power query?, power query variable input from excel, excel power query, get & transform, business intelligence, power bi

Id: -0ThYDheUwM

Length: 12min 14sec (734 seconds)

Published: Sat Mar 26 2022