One query, two environments, no editing the source step by hand. You build against a test CSV, then point the same query at the production SharePoint file by changing one dropdown. A parameter holds the path, the query reads the parameter, and switching environments stops being a find-and-replace job.
How to do it
You have a query loading from a local test file, and a production copy of the same file sitting on SharePoint.
- Home → Manage Parameters → New Parameter. Name it
SourcePath, type Text, set Suggested Values to "List of values", and add two entries: your test path (C:\Data\sales-test.csv) and the production URL. Set Current Value to the test path. - Open your query, click the Source step, and in the formula bar swap the hard-coded path for the parameter name:
File.Contents(SourcePath)instead ofFile.Contents("C:\Data\sales-test.csv"). - To switch environments, go back to Manage Parameters, open the
SourcePathdropdown, pick the production value, and Close & Apply.
The query body never changes. Only the parameter does.
Why it works
A parameter is a named value that Power Query treats as a first-class input. Anywhere a query expects a literal - a path, a URL, a filter date, a row count - you can hand it the parameter name instead, and every step downstream uses whatever the parameter currently holds. Because the suggested values are a fixed list, you pick from a dropdown rather than retyping a path you will inevitably fat-finger. The Source step resolves the parameter at refresh time, so flipping dev to prod is a single change in one place rather than hunting through M code.
When not to use it
This works cleanly when both environments are the same shape - a CSV locally and the same CSV on SharePoint, both read by File.Contents. If dev and prod need genuinely different connectors - a flat file in test, a SharePoint list or a database in production - a path parameter is not enough, because the M function that loads each one differs too. That is a job for a function that branches on the environment, not a single dropdown.
The full version
Wrapping the load logic in a function so it handles a different connector per environment is the foundation for a lot of reusable Power Query - see Magic Monday: custom functions (22 June).