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".
Worked example — parameters
Why
Rohan Patil in Pune and Liam Murphy abroad both need a clear, repeatable way to use parameters on a HomeNest Supplies sheet without breaking totals.
What
Parameters is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for parameters.
- Enter a small made-up value for Rohan Patil using ₹ where money appears.
- Add a second value for Liam Murphy using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once parameters is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of parameters on a blank sheet for Skyline Clinics.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Vikram Rao and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Yuki Tanaka.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply parameters to a 5-row made-up Skyline Clinics table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with parameters.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as SkylineClin_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Rows in the import (Easy): How many rows are in the Order ID column?
- Clean one customer name (Easy): Remove the extra spaces from the first Customer name.
- Fix the case of a city (Easy): Show the first City in normal case, like "Pune".
- Total quantity (Easy): Add up the Qty column.
- How many spaces were extra? (Medium): How many extra spaces does the first Customer name have?
- Duplicate order check (Medium): How many times does Order ID "ORD-502" appear?
- Orders from one city, any case (Medium): How many rows have City "Mumbai", in any case?
- Amount by channel (Medium): Add up the Amount column for rows where Channel is "App".
- Average order amount (Medium): Find the average of the Amount column, rounded to 0 decimals.
- Rows after Remove Duplicates (Hard): How many rows are left after the extra copies of "ORD-502" are removed?
- Biggest store order (Hard): What is the largest Amount where Channel is "Store"?
- Price per unit (Hard): For the first row, divide Amount by Qty.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
