Using Parameters and Dynamic Inputs for Reusable Queries

Why parameters matter for reusable Power Query

Reusable queries are queries that can be run again tomorrow (or by someone else) with minimal edits. In Power Query, the biggest blocker to reuse is “hard-coded” values: a fixed file path, a fixed date range, a fixed region name, a fixed threshold, or a fixed sheet name. Parameters and dynamic inputs solve this by separating what changes (inputs) from what stays the same (transformation logic).

A parameter is a named value that can be referenced inside one or more queries. When you change the parameter, every dependent query updates consistently. Dynamic inputs are broader: they include parameters, values read from an Excel table or named range, values derived from the current date/time, and values passed through a function. The goal is the same: make the query logic stable while allowing controlled variability.

Common scenarios where parameters pay off

• Environment switching: Dev vs. Prod file paths, different server names, different folder roots.
• Time windows: “Last 30 days”, “Current month”, “Fiscal year to date”.
• Filtering: Region, business unit, product category, customer segment.
• Thresholds: Minimum order value, outlier cutoffs, tolerance bands.
• Schema variability: A sheet name that changes monthly, or a column list that should be selected dynamically.

Creating and using built-in parameters

Power Query supports parameters as first-class objects. In Excel’s Power Query Editor, you can create them from the ribbon and then reference them in M code. This is the most straightforward approach when you want a controlled, typed input with optional allowed values.

Step-by-step: Create a parameter for a folder root

This example assumes you want to avoid editing a folder path inside M every time the workbook moves.

• 1) Create the parameter: In Power Query Editor, choose Manage Parameters > New Parameter.
• 2) Name it: Use a clear name like pFolderRoot.
• 3) Set type: Choose Text.
• 4) Set current value: Example: C:\Data\Sales.
• 5) Optional: Add a description like “Root folder for monthly sales extracts”.

Now reference it in a query that reads from a folder. In M, you typically pass the parameter into the connector function.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

let    Source = Folder.Files(pFolderRoot),    VisibleFiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)in    VisibleFiles

When pFolderRoot changes, the folder connector points to the new location without touching the query steps.

Step-by-step: Create a parameter with allowed values (dropdown behavior)

Parameters can be constrained to a list of allowed values. This is useful for region filters or environment toggles.

• 1) New parameter: Name it pRegion.
• 2) Type: Text.
• 3) Suggested values: Choose List of values.
• 4) Values: North, South, East, West, All.
• 5) Current value: Start with All.

Use it in a filter step:

let    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],    Filtered = if pRegion = "All"        then Source        else Table.SelectRows(Source, each [Region] = pRegion)in    Filtered

This pattern avoids duplicating queries per region and keeps the logic in one place.

Dynamic inputs from Excel tables (control panel pattern)

Built-in parameters are great, but many teams prefer a “control panel” sheet where business users can change inputs without opening Power Query Editor. The pattern is: store inputs in an Excel table (or named range), read them with Excel.CurrentWorkbook(), and use them as variables in M.

Designing the control table

Create an Excel table named tblParams with two columns: Parameter and Value. Example rows:

• FolderRoot | C:\Data\Sales
• Region | West
• StartDate | 2025-01-01
• MinAmount | 100

Keeping parameters as rows (not columns) makes it easy to add new inputs without changing the table structure.

Step-by-step: Read a single value from tblParams

In Power Query, create a helper query named qParams that loads tblParams and converts it into a record for easy lookup.

let    Source = Excel.CurrentWorkbook(){[Name="tblParams"]}[Content],    Typed = Table.TransformColumnTypes(Source, {    {"Parameter", type text}, {"Value", type text}    }),    AsRecord = Record.FromTable(Typed)in    AsRecord

Now in any query, you can retrieve values like qParams[FolderRoot] or qParams[Region].

Example: using a folder root from the control table:

let    FolderRoot = qParams[FolderRoot],    Source = Folder.Files(FolderRoot)in    Source

Typing and converting control-table values

Values from Excel often arrive as text. Convert them explicitly to avoid subtle bugs (especially with dates and numbers).

let    StartDateText = qParams[StartDate],    StartDate = Date.From(StartDateText),    MinAmountText = qParams[MinAmount],    MinAmount = Number.From(MinAmountText)in    [StartDate = StartDate, MinAmount = MinAmount]

If your control table stores true Excel dates (not text), you may receive them as date or datetime already, but it’s still good practice to enforce types where you rely on them.

Validation: fail fast with clear messages

Reusable queries should fail with helpful errors when inputs are missing or invalid. You can validate parameters and raise an error early.

let    Region = qParams[Region],    Allowed = {"North","South","East","West","All"},    Check = if List.Contains(Allowed, Region)        then Region        else error "Invalid Region parameter. Use North, South, East, West, or All."in    Check

This prevents confusing downstream errors like “column not found” or empty results that look like data issues.

Using Date/Time functions for rolling windows

Not all dynamic inputs need to come from the user. Many refresh scenarios depend on “today”. Power Query provides DateTime.LocalNow(), Date.From(), and date arithmetic to build rolling filters.

Example: last N days (N from a parameter)

Combine a numeric parameter (or control-table value) with today’s date to compute a start date.

let    DaysBack = Number.From(qParams[DaysBack]),    Today = Date.From(DateTime.LocalNow()),    StartDate = Date.AddDays(Today, -DaysBack),    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],    Typed = Table.TransformColumnTypes(Source, {{"OrderDate", type date}}),    Filtered = Table.SelectRows(Typed, each [OrderDate] >= StartDate and [OrderDate] <= Today)in    Filtered

Note that DateTime.LocalNow() uses the local machine time zone. If your organization needs a consistent time zone, consider storing a “business date” in the control table or using a fixed offset strategy.

Example: current month to date

let    Today = Date.From(DateTime.LocalNow()),    MonthStart = Date.StartOfMonth(Today),    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],    Typed = Table.TransformColumnTypes(Source, {{"OrderDate", type date}}),    Filtered = Table.SelectRows(Typed, each [OrderDate] >= MonthStart and [OrderDate] <= Today)in    Filtered

This pattern is especially useful for dashboards that should always show the latest period without manual edits.

Turning queries into functions (parameterized transformations)

Parameters become even more powerful when you encapsulate logic as a function. A function is a query that accepts inputs and returns a value (often a table). This lets you reuse the same transformation steps across multiple sources without copying and pasting.

Concept: function = reusable recipe with inputs

Instead of writing “one query per file/sheet/region”, you write one function that takes the variable parts (like a table, a file path, or a region) and applies the same shaping rules.

Step-by-step: Create a function that filters by a date range

Assume you already have a table with an OrderDate column. Create a new blank query and paste M that defines a function:

(InputTable as table, StartDate as date, EndDate as date) as table =>let    Typed = Table.TransformColumnTypes(InputTable, {{"OrderDate", type date}}),    Filtered = Table.SelectRows(Typed, each [OrderDate] >= StartDate and [OrderDate] <= EndDate)in    Filtered

Name it fxFilterByDateRange. Now call it from another query:

let    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],    StartDate = Date.From(qParams[StartDate]),    EndDate = Date.From(qParams[EndDate]),    Result = fxFilterByDateRange(Source, StartDate, EndDate)in    Result

This keeps the filtering logic in one place. If you later need to change the definition of “date range” (for example, include time, or handle null dates), you update the function once.

Step-by-step: Function that selects columns based on a parameter list

Column selection often becomes brittle when schemas evolve. A dynamic approach is to store a comma-separated list of desired columns in the control table and select only those that exist.

Control table row: KeepColumns | OrderID,OrderDate,Region,Amount

Create a function:

(InputTable as table, KeepList as list) as table =>let    Existing = List.Intersect({Table.ColumnNames(InputTable), KeepList}),    Result = Table.SelectColumns(InputTable, Existing, MissingField.Ignore)in    Result

Call it like this:

let    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],    KeepText = qParams[KeepColumns],    KeepList = List.Transform(Text.Split(KeepText, ","), each Text.Trim(_)),    Result = fxKeepExistingColumns(Source, KeepList)in    Result

This makes your query resilient: if a column is missing in a particular refresh, the query still runs (and you can separately add checks if missing columns should trigger an error).

Parameterizing data source details safely

Some connectors and privacy settings can behave differently depending on how dynamic your source is. A practical approach is to parameterize at a sensible boundary: keep the connector stable when possible, and parameterize the parts that are expected to change.

Example: switch between two known environments

Instead of allowing any arbitrary server name, restrict to a known set (Dev/Prod). This reduces accidental refresh failures and makes governance easier.

let    Env = qParams[Environment],    Server = if Env = "Prod" then "SQLPROD01" else "SQLDEV01",    Database = "SalesDW",    Source = Sql.Database(Server, Database)in    Source

Even if you are not using SQL, the same idea applies to folder roots or SharePoint site URLs: constrain choices where possible.

Using optional parameters and defaults

Sometimes you want a parameter to be optional: if the user leaves it blank, the query should use a default behavior. In M, you can implement this with checks for null/empty strings.

Example: optional region filter

let    RegionRaw = try qParams[Region] otherwise null,    Region = if RegionRaw = null or Text.Trim(RegionRaw) = "" then "All" else RegionRaw,    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],    Filtered = if Region = "All"        then Source        else Table.SelectRows(Source, each [Region] = Region)in    Filtered

try ... otherwise is useful when the control table might not yet contain a row for a new parameter. This helps you evolve the model without breaking existing workbooks.

Making parameters discoverable and maintainable

Reusable queries are not just about code; they are about clarity for the next person (including future you). A few practices make parameter-driven solutions easier to maintain.

Naming conventions

• Prefix parameters: pStartDate, pRegion, pFolderRoot.
• Prefix functions: fxFilterByDateRange, fxKeepExistingColumns.
• Prefix helper queries: qParams, qConfig.

Centralize parameter retrieval

Avoid repeating “read from tblParams and convert types” in every query. Instead, create one helper query that outputs a record with typed fields, then reference it everywhere.

let    Raw = Excel.CurrentWorkbook(){[Name="tblParams"]}[Content],    Typed = Table.TransformColumnTypes(Raw, {{"Parameter", type text}, {"Value", type any}}),    AsRecord = Record.FromTable(Typed),    StartDate = Date.From(AsRecord[StartDate]),    EndDate = Date.From(AsRecord[EndDate]),    MinAmount = Number.From(AsRecord[MinAmount]),    Region = Text.From(AsRecord[Region]),    Output = [StartDate=StartDate, EndDate=EndDate, MinAmount=MinAmount, Region=Region]in    Output

Now your other queries can do qConfig[StartDate] and know it is already typed.

Document parameters in the workbook

Even if you use built-in parameters, consider mirroring them in a visible “Inputs” sheet: list each parameter name, what it controls, allowed values, and examples. This reduces the chance of someone changing a value and unintentionally altering the logic.

Practical mini-project: one query, many outputs via parameters

This mini-project shows how parameters enable multiple outputs without duplicating transformation steps. You will create a single base query and then create lightweight “views” that reference it with different parameter values.

Step-by-step: Base query + parameter-driven views

• 1) Create control table: tblParams with StartDate, EndDate, Region.
• 2) Create qConfig: read and type the parameters into a record.
• 3) Create base query qSalesBase: load the sales table and apply stable transformations (types, calculated columns, standard column names). Keep it unfiltered or lightly filtered.
• 4) Create view query qSalesFiltered: reference qSalesBase and apply filters using qConfig.

Example view query:

let    Source = qSalesBase,    StartDate = qConfig[StartDate],    EndDate = qConfig[EndDate],    Region = qConfig[Region],    DateFiltered = Table.SelectRows(Source, each [OrderDate] >= StartDate and [OrderDate] <= EndDate),    RegionFiltered = if Region = "All"        then DateFiltered        else Table.SelectRows(DateFiltered, each [Region] = Region)in    RegionFiltered

Now you can create additional view queries that use different parameter sets. If you want multiple outputs simultaneously (e.g., West and East at the same time), store a list of regions in a control table and generate a table per region via a function, or produce one combined table with a region list filter.

Advanced dynamic input: filtering by a list from Excel

Single-value parameters are common, but list-based inputs are often more realistic: “include these product lines” or “exclude these customers”. You can store a list in an Excel table and use it to filter.

Step-by-step: include list filter

Create an Excel table named tblIncludeRegions with one column Region and values like West, North.

In Power Query:

let    IncludeTable = Excel.CurrentWorkbook(){[Name="tblIncludeRegions"]}[Content],    IncludeList = List.Distinct(List.Transform(IncludeTable[Region], each Text.From(_))),    Source = qSalesBase,    Filtered = Table.SelectRows(Source, each List.Contains(IncludeList, [Region]))in    Filtered

This approach is user-friendly: users edit the list in Excel, refresh, and the query updates. It also avoids long “OR” conditions in M.

Performance considerations with parameters

Parameters can improve performance when they reduce the amount of data processed (for example, filtering early by date). But they can also hurt performance if they prevent query folding in certain connectors or force Power Query to re-evaluate expensive steps.

Practical guidelines

• Filter as early as possible when it reduces row count significantly, especially for large sources.
• Keep connector steps simple and avoid unnecessary dynamic construction of source expressions when using foldable sources.
• Cache intermediate results carefully: if you reference the same base query multiple times, consider whether it should be loaded once and referenced, or whether it should remain a connection-only query depending on your model.
• Prefer typed parameters (date/number) over text when possible to reduce conversion ambiguity.