10. Parameters in Power Query (and Other Kinds of Parameters)
10.2 Creating a Parameter (Manage Parameters Dialog)
Start with a problem
Zoya's report points at the test server QCTEST01. Before publishing she must change the server name inside four queries, and she always misses one.
Explain simply
A parameter is a named value stored in Power Query. Queries use its name, so you change the value in one place.
Show the data
Values to type in Manage Parameters (fictional servers):
| Field | Value |
|---|---|
| Name | ServerName |
| Description | SQL Server for quick-commerce data |
| Required | ticked |
| Type | Text |
| Suggested values | List of values: QCTEST01, QCSERVER01 |
| Current value | QCTEST01 |
About this data: This table uses columns: Field, Value. This table uses columns: Parameter, Type, Current value, Used in. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
- Power Query Editor › Home › Manage Parameters › New Parameter.
- Type the Name
ServerNameand the description. - Required: ticked. Type: Text.
- Suggested values: List of values › type QCTEST01 and QCSERVER01.
-
Current value: QCTEST01 › OK.
Your result should look like this:
Queries pane ServerName (parameter icon) = QCTEST01 -
Create a second parameter
MinAmount: Type Decimal Number, Current value300. Load the practice CSV asOrdersand use Amount › Number Filters › Greater Than Or Equal To › choose Parameter › MinAmount.Your result should look like this:
Orders rows 301, 303, 305, 307 (4 rows)
Show the result
Your result should look like this (fictional sample):
| Parameter | Type | Current value | Used in |
|---|---|---|---|
| ServerName | Text | QCTEST01 | Sql.Database (lesson 10.4) |
| MinAmount | Decimal Number | 300 | Orders filter: 4 orders, ₹1,500 |
Explain what happened
Power Query stored each parameter as a tiny query with settings (type, list, required). The Orders filter step now reads each [Amount] >= MinAmount instead of a typed number.
Explore further
- Right-click a parameter › untick Enable load: a parameter should not become a table.
- Change values later from Report view: Home › Transform data › Edit parameters.
Detailed reference for this lesson:
Steps in Power BI
- Open the Power Query Editor › Home › Manage Parameters › New Parameter.
- Fill in the fields:
- Name:
ServerName(no spaces makes M code easier). - Description: "SQL Server for quick-commerce data" (shown as a tooltip).
- Required: ticked (the value cannot be empty).
- Type: Any, Text, Decimal Number, Date/Time, Date, Time, Date/Time/Timezone, Duration, True/False, Binary.
- Suggested Values: Any value, List of values (type allowed values, e.g. QCTEST01, QCSERVER01), or Query (a list query supplies the values).
- Default Value (for lists) and Current Value.
- Name:
- Click OK. The parameter appears in the Queries pane with a special icon and shows its current value.
- To change it later: select it in the Queries pane and edit Current Value, or in Report view use Home › Transform data › Edit parameters.
Parameter from a query
Create a list query, for example a blank query = {"Pune","Mumbai","Nashik","Sambhaji Nagar","Kolhapur","Nagpur"}, or right-click a column › Drill Down to turn DarkStore[City] into a list. Choose Suggested Values › Query and pick it. The drop-down then nehmi matches your data.
Now let us move on – and see where to actually use this parameter.
आता पुढे जाऊया – हा parameter प्रत्यक्षात कुठे वापरायचा ते बघू.
अब आगे बढ़ते हैं – यह parameter असल में कहाँ इस्तेमाल करना है, वह देखते हैं.
Ravindra Bagale's Tip
A common mistake is choosing the wrong parameter type, for example Text for a date limit. Pick the type that matches how it will be used (Date/Time for RangeStart, Text for a server name) and add Suggested values from a list when only a few values are valid. This matters for both exams and interviews.
Ravindra Bagale's Tip – मराठी
एक common चूक म्हणजे चुकीचा parameter type निवडणं, उदाहरणार्थ date limit साठी Text. तो कसा वापरला जाणार त्याला जुळणारा type निवडा (RangeStart साठी Date/Time, server name साठी Text) आणि फक्त थोड्याच values valid असतील तर list मधून Suggested values जोडा. हे exam आणि interview दोन्हीसाठी important आहे.
Ravindra Bagale's Tip – हिंदी
एक common गलती है गलत parameter type चुनना, जैसे date limit के लिए Text. वह जैसे इस्तेमाल होगा उससे मेल खाता type चुनो (RangeStart के लिए Date/Time, server name के लिए Text) और जब कुछ ही values valid हों तो list से Suggested values जोड़ो. यह exam और interview दोनों के लिए ज़रूरी है.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| The parameter cannot be picked in the filter dialog | Parameter type is Any or does not match the column | Set the type to match (Decimal Number for Amount) |
| The parameter appears as a table in the model | Enable load left on | Untick Enable load |
M shows #"Server Name" everywhere |
Spaces in the name | Use names without spaces: ServerName |
Practise independently
Practice task
Change MinAmount to 400. How many orders and what total remain?
Hints
- Amounts: 400, 200, 300, 150, 450, 250, 350, 0.
Solution
MinAmount 400 keeps 301 (₹400) and 305 (₹450): 2 orders, ₹850. (With 300 it was 4 orders, ₹1,500.)
Think like a professional
Name parameters for what they mean (ServerName, FolderPath, StartDate) and add descriptions. A parameter is a setting for the author, not a slicer for viewers.
Revision card