10.8 Parameters
A parameter (बदलता येणारे मूल्य, जे अनेक ठिकाणी वापरता येते) stores a value such as a folder path or a minimum amount, used in several queries.
Steps in Excel – folder path parameter
- Home › Parameters › Manage Parameters › New Parameter.
- Name
StatementFolder› Type Text › Current ValueD:\Statements\SavingsAC\› OK. - Open the folder query's Source step (gear icon) › change Folder path to use the parameter (choose Parameter in the drop-down next to the box).
- When the folder moves, change only the parameter value (Manage Parameters) › Refresh.
Parameter from a worksheet cell (common Excel trick): put the value in a named cell MinAmount (e.g. 199). In Power Query: select that cell's Table/range › From Table/Range › right-click the value › Drill Down. Name the query pMinAmount, then filter Orders with each [Amount] >= pMinAmount. Users change the cell and click Refresh.
Ravindra Bagale's Tip
File/folder che path M code madhe hard-code kele ki laptop badalla kiwa folder hallavla tar sagle queries fail hotat – khup students la mag pratyek query ughadavi lagte. Path parameter madhe theva. Ani cell-based parameter vaparla tar tyala spasht label dya – "Change this and click Refresh".
Practice task
Create a StatementFolder parameter for the bank-statement query. Create a worksheet-cell parameter for a minimum order amount and use it to filter a query.