For example, if we filter a column to select all values greater than 50, the 50 will be a hardcoded value in the M code.Or if we import a CSV file, the file path is hardcoded into the query.
![]() What if we want to change these values or file paths Obviously, we could edit the query each time, which would be very time-consuming. We use the term variable when writing VBA, or maybe the terms conditions, criteria or arguments when writing Excel formulas, these are all effectively the same thing. Parameters, variables, conditions, criteria and arguments are all values we can change to get a different output. Microsoft decided to use the term Parameter when designing Power Query. In this post, we will be using cells values as parameters; therefore, by changing the cell value we can change the result of a query. The parameter could be contained within a CSV file, or as a database setting. But well focus on the situation youre most likely to encounter, which is a cell value. As Power Query develops, new and easier to use features are introduced. The method I want to show you I believe is currently the easiest and best to implement. Date Parameters In Microsoft Query Wizard Download The ExampleDownload the example files It may be tempting just to read the text below, but you will retain the information much better if you work along with the examples. Date Parameters In Microsoft Query Wizard Download The FilesSubscribers can download the files from the Downloads section. All the examples in this post use Example 8 Using Parameters.xlsx from the downloads. Create the query The first step is to create a query as normal. Select any cell in the source table and click Data - From TableRange from the ribbon. Make the following transformations Date column Click on the Date and Time icon next to the Date header, select Date from the menu. Select the Date column header, then click Transform - Date - Month - End Of Month Filter the Date column to only include 31 January 2019. ![]() Ensure the Date column is still selected, then click Home - Remove Columns Sold By column On the Sold By column, click on the filter icon and ensure only David is selected. With the Sold By column still selected, click Home - Remove Columns Thats enough transformations for now. The Table should look like this: From the source data we created a Table which shows the products sold by David in January 2019. But what if we want the products sold by Sally in March 2019, or Mark in March 2019 This is where the parameters come in. In the next section, we will create some parameters to dynamically change the name and date.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |