You build a set of queries, and they work perfectly. Then you email the workbook to a colleague, and everything breaks. Their computer has a different file path, so no query can find its source. Or you need to change a date filter, and it is buried in ten separate steps. Parameters solve both headaches. They store a value once, then feed it into every query that needs it.
This guide shows how to make queries dynamic and portable. First, it explains what a parameter is. Then it covers creating one and using it in paths and filters. Everyday uses and a full troubleshooting section follow. By the end, you will build queries that travel and adapt.
What a Parameter Is
A parameter is a named value you can change. You set it once, then plug it into your steps. Many queries can share the same parameter. So one edit updates them all, as below.
Think of it like a variable in a formula. You change the variable, and the result follows. A parameter works the same way for queries. As a result, your logic stays in one place.
Why Hard-Coding Hurts
Hard-coding buries a value deep inside a step. A file path or a date sits locked in the code. So changing it means editing every query by hand. Worse, one missed edit breaks the refresh.
Create a Parameter
Making a parameter takes only a few clicks. You open the Manage Parameters dialog. Then you give it a name, a type, and a value. So it is ready to use right away, as below.
Use a Parameter for a File Path
The classic use is a source path. You replace the hard-coded path with the parameter. Then the query reads its location from that value. So moving the file means changing one parameter.
Use a Parameter in a Filter
Parameters also drive dynamic filters. Suppose you want sales since a chosen date. You store that date in a parameter. Then the filter compares each row to it.
Everyday Uses
Parameters fit far more than file paths. They power dates, folders, and even environments. Each use removes a hard-coded value from your steps. The infographic below shows four common cases.
Troubleshooting Parameters
All three problems below are the most common. Each has a clear cause and a quick fix.
The path is still hard-coded after I made the parameter
Creating a parameter does not wire it in for you. The query keeps its old value until you update it. First, select the Source step you want to change. Then edit that step to reference the parameter. Replace the fixed text with the parameter name. Refresh to confirm the query now follows it. Repeat for any other query that should use it.
The parameter type causes an error
A type mismatch is a common trip-up. A Text parameter cannot slot into a date filter. So Power Query throws an error on refresh. First, open Manage Parameters and check the Type. Then match it to how the value is used. A date filter needs a Date type, for example. After you fix the type, the step accepts it.
Changing the value did nothing
A new parameter value needs a refresh to apply. The queries still show the old result until then. First, change the Current Value in Manage Parameters. Then close the editor and click Refresh All. The steps rerun with the new value. So the report finally reflects your change.
Frequently Asked Questions
- What is a parameter in Power Query?It is a named value you set once and reuse. You then plug it into your query steps. Many queries can share the same parameter. So one edit updates them all.
- How do parameters make a workbook portable?They move the file path into one place. So a new machine needs just one change. Without a parameter, every query breaks on a new PC. Therefore parameters make sharing far easier.
- Can I use a parameter in a filter?Yes, and it is a very common use. Create a Date parameter, then filter your date column by it. Change the value and refresh to see a new window. So one value reshapes the report.
- Why does my parameter change not take effect?Because the queries need a refresh to rerun. So update the Current Value first. Then click Refresh All to apply it. After that, the new value shows up.