As a simple explanation, a parameter is just a normal query, in which we drill down into the value itself and load as a connection. In this example, we will be using an Excel Table as the source, but it could equally be in named range, CSV, or any other data source we can get into Power Query. On the worksheet which contains the query output table, create two tables with single values in them:. Pay close attention to the data type. The Sold By column in the original query above is a text data type, and the data type in this query is also text.
We need these to be identical. Within Power Query right-click on the value and select Drill Down from the menu. The window will change to a view we have not seen before, the Text Tools window:. Make a note of the query name, which is SoldBy no space , as shown in the screenshot above. Compared to the text parameter we created above, there is one additional step we need to make. In the original query, the Date column had a date type at the point it was filtered, therefore we need a date type for the parameter too.
After changing the Date column to a date type , right-click on the value and click Drill Down. To do this, we are going to make some basic changes to the M code. We could use the Advanced Editor, or the Formula Bar. Important information: M code is case sensitive SoldBy and soldby are not the same. Next, we will apply the Date parameter. Find the step where we hardcoded 31 January as the date. You can now set-up any Power Query hardcoded value as a parameter.
I find the most useful things to set-up as parameters are:. By entering your email address you agree to receive emails from Excel Off The Grid. Do you need help adapting this to your needs? By taking the time to understand the techniques and principles in this post and elsewhere on this site you should be able to adapt it to your needs. What next? Check out the latest posts:.
Also if you use the table to filter out one of them — the table only returns to topmost in the list even if it has been deselected.
By using an Inner Join you can include the items which exist in both the data table and the lookup table. In recent work, adding parameters after a number of transformations resulted in the query breaking parameters used to point to a file path and folder. What is the best point in query development to add parameters?
I am thinking quite early on, say at the stage before major transformations are done e. Any thoughts? I normally add parameters at the end once the query is working how I want. What was the error? Which build version of Excel are you using, as Power Query has been a bit flakey in some versions and would crash occasionally.
Thank you so much for the brilliant explanation. I had been trying to no avail to understand parameters. On your worksheet, click a cell anywhere in the external data range that was created with the parameter query. On the Data tab, in the Connections group, click Properties.
To use the new custom prompt and refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group. Then click Refresh. Note: If you want to save your changes to the custom prompt, save the workbook. You can also edit the query to change the prompt.
To refresh the data, click the arrow next to Refresh All on the Data tab, in the Connections group, and then click Refresh. You can always ask an expert in the Excel Tech Community or get support in the Answers community. Change the custom prompt for a parameter query On your worksheet, click a cell anywhere in the external data range that was created using the parameter query.
In the Enter Parameter Value dialog box, the new prompt is displayed. On your worksheet, type the values that you want to use as criteria in the query.
Click a cell anywhere in the external data range that was created with the query. Click Get the value from the following cell. The reason is that as soon as you try to say OK to any parameter in that list whether modifying or creating a new one , it appears to try to validate the current value of each of the listed parameters:. This is unfortunate, as it means that you'd need to kick over to a blank query to create any new Parameters or debug any existing ones.
If you do that the M code upon the initial creation comes up as:. If the required setting is false, then the manage queries dialog can still be used without forcing an update! Having said that, I got mixed results doing this. Sometimes the Parameters were not presented in my drop down list.
Editing those queries and restoring the full meta tag to the end resolved that immediately. It makes the usage of them a lot more convenient. Maybe a bit off topic, but I was hoping you meant the following with dynamic parameters What I find really annoying, is that when you link a parameter to a list of values, this list of values does not get updated unless you go into the 'Edit Query'-window.
For example: you have a parameter that links to a list of countries. That list is for example a distinct select from the customer table on a sql server. Using that parameter you filter for example that same customer table. However, when a new country is added that was not there before, you will not see it in the list.
You first have to edit the list-query, refresh the preview, and then the new country will show. So basically, the parameter uses a list that is static, not dynamic. I have not found the way to first refresh the list query, and then the other queries in Power BI Desktop, that is Hi Ken Great post. You will be pleased to know that dynamic parameters do not have to render the manage parameters window useless.
Simply mark your dynamic parameters as not required unchecked the required box when first creating the parameter and then enjoy being able to add and edit your static parameters without breaking your dynamic parameters. Honestly, neither have I, mainly because I've been unhappy with the static nature of a parameter. Based on the above, what about making a query that creates a list, then filling that list to the parameter?
The query should be dynamic then. Would that work? Pingback: Power BI colors, data models, release notes and more June 4, Guy in a Cube. Thank you so much! Never new about this meta tag. That way, I could have a model query that I could use to control the function with. Now I know how to do it. Thanks again! Ken, Thank you for this really useful post.
I have followed your recommendations and now have useful dynamic parameters. I am unable to get them to work in the Source applied step. Would you expect them to work in a source step or is this their normal behaviour? When I use static Parameters in the source step they work - i am opening excel files on my computer. Do you mean that they are not showing up in drop downs and such? To be honest, I didn't try using them in a Source step, but I would have expected them to either work or not, not be selective about where Ken, To clarify the dynamic parameters do show up in the drop downs and you can choose them but you get the following message Formula.
Firewall: Query 'ConnectToDailyAllocation' step 'Source' references other queries or steps, so it may not directly access a data source.
Please rebuild this data combination. If I choose a static parameter it will work without this problem. Ah, yes, sorry. I was reading on this over the weekend, and trying to combine data from two sources in one statement like a file path and the file in a single step causes this issue.
Once you have the data in past the source step , then you should be fine. Hello, thanks for your post. What if I have two rows I need to use for criteria, i. Or bigger range. Now we can use these parameters in our main query For example, here I am connecting to the demo file and filtering on date Then I go into the Advanced Editor and change the code as follows: And the query now works but is controlled by those 2 named ranges in my Excel sheet.
Extra Notes Just for clarity purposes I tend to name my parameter queries beginning with a p. So pFilePath and pCutoffDate to make it obvious in my code that these are parameter queries.
0コメント