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
If you hard-code file/folder paths in the M code, all queries fail when you change laptops or move the folder – and many students then have to open every query. Keep the path in a parameter. And if you use a cell-based parameter, give it a clear label – "Change this and click Refresh".
Ravindra Bagale's Tip – मराठी
File/folder चे path M code मध्ये hard-code केले की laptop बदलला किंवा folder हलवला तर सगळ्या queries fail होतात – बऱ्याच students ना मग प्रत्येक query उघडावी लागते. Path parameter मध्ये ठेवा. आणि cell-based parameter वापरला तर त्याला स्पष्ट label द्या – "Change this and click Refresh".
Ravindra Bagale's Tip – हिंदी
File/folder के path M code में hard-code किए तो laptop बदलने या folder हिलाने पर सारी queries fail हो जाती हैं – फिर बहुत से students को हर query खोलनी पड़ती है. Path को parameter में रखो. और cell-based parameter इस्तेमाल करो तो उसे साफ़ label दो – "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.