Parameterizing Dataflow Gen2 Queries with Pipeline Integration: Passing Dynamic Values to Power Query for Reusable Ingestion Flows

Introduction

Here's a scenario you've probably lived through: you build a Dataflow Gen2 that ingests sales data for a specific region, hardcoding "EMEA" into the query. It works perfectly. Then someone asks for APAC data using the same transformation logic. You clone the dataflow, change the region string, and now you're maintaining two nearly identical assets. Six months later, there are five regional dataflows, a schema change breaks all of them simultaneously, and you're the one spending a Saturday afternoon fixing each one by hand.

This is the parameterization problem, and it's one of the most common sources of unnecessary technical debt in Fabric implementations. The good news is that Dataflow Gen2 supports Power Query parameters natively, and Microsoft Fabric's data pipeline engine can inject values into those parameters at runtime — meaning you can build a single, reusable dataflow that behaves differently depending on what the pipeline tells it to do. One dataflow, many execution contexts. That's the goal.

By the end of this lesson, you'll have a complete mental model of how Power Query parameters work in Dataflow Gen2, how Fabric pipelines pass dynamic values through the Dataflow activity, and how to architect an ingestion flow that is genuinely reusable across environments, date ranges, regions, or any other dimension that varies between runs.

What you'll learn:

• How to define and use Power Query parameters within Dataflow Gen2
• How to reference parameters inside query steps to make transformation logic dynamic
• How to configure a Fabric pipeline to pass runtime parameter values into a Dataflow activity
• How to design a reusable, multi-tenant or multi-context ingestion pattern using a single dataflow
• How to debug the most common parameterization failures before they reach production

---

Prerequisites

You should be comfortable working in Dataflow Gen2 and writing basic Power Query M. If you need a foundation, review Ingesting Data with Dataflow Gen2: Power Query Skills in Microsoft Fabric before continuing.

You should also understand how Fabric pipelines work at a conceptual level — activities, runs, and how pipelines consume other Fabric items. Orchestrating Loads with Fabric Data Pipelines: Copy Activities, Parameters, and Schedules covers this well.

You'll need a Fabric workspace with at least Contributor access. If you're working with a trial, see Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace to get set up.

---

Understanding How Parameterization Works in This Stack

Before writing a single line of M code, let's get the plumbing clear. There are three distinct layers involved, and confusing them is the fastest route to frustration.

Layer 1: Power Query Parameters. Inside Dataflow Gen2's Power Query editor, you define parameters — named, typed values that queries can reference. These behave like variables that are evaluated before your query logic runs. If you have a parameter called RegionCode with a default value of "EMEA", every query in that dataflow can reference RegionCode and get that string back.

Layer 2: Dataflow Gen2's Parameter Binding Surface. Dataflow Gen2 exposes parameters to the outside world. When you define a parameter in Power Query and mark it appropriately, the Dataflow activity in a Fabric pipeline can see that parameter and bind a value to it at runtime. This is the bridge between the static world of Power Query and the dynamic world of pipeline orchestration.

Layer 3: Pipeline Parameters and Dynamic Expressions. Fabric pipelines have their own parameter system. A pipeline can accept parameters at trigger time (from a schedule, a manual run, or a parent pipeline), and it can use those values inside activity settings using the @pipeline().parameters.ParameterName expression syntax. Those pipeline-level values are what ultimately flow into the dataflow parameter binding.

The execution sequence looks like this: a pipeline run starts with a specific parameter value (say, "2024-01" for a monthly load), the Dataflow activity receives that value through its settings, the dataflow engine injects it into the Power Query parameter, and the query logic executes using that injected value.

---

Defining Parameters in Dataflow Gen2's Power Query Editor

Open your Dataflow Gen2 in the Fabric portal and navigate to the Power Query editor. The parameter management interface lives under the Home tab in the ribbon. Look for the Manage parameters option — clicking it opens a dialog where you can create, edit, and delete parameters.

Let's build a concrete example. Imagine you're ingesting monthly transaction data from an Azure SQL Database, and the source table is partitioned by a fiscal_month column in YYYY-MM format. You want the pipeline to tell the dataflow which month to extract.

In the Manage Parameters dialog, create a new parameter with these settings:

• Name: FiscalMonth
• Type: Text
• Required: Yes (unchecked is fine for dev, but mark it in your documentation)
• Default Value: 2024-01

Create a second parameter for the source environment:

• Name: SourceSchema
• Type: Text
• Default Value: dbo

Click OK to save. You've now established two named values that the entire dataflow can reference.

Referencing Parameters in Query Steps

Now create a query that connects to your SQL source. After connecting and loading the initial table preview, open the Advanced Editor for that query step. The raw M will look something like:

let
    Source = Sql.Database("your-server.database.windows.net", "SalesDB"),
    Navigation = Source{[Schema="dbo", Item="Transactions"]}[Data],
    FilteredRows = Table.SelectRows(Navigation, each [fiscal_month] = "2024-01")
in
    FilteredRows

The problem is obvious: "2024-01" and "dbo" are hardcoded strings. Replace them with parameter references:

let
    Source = Sql.Database("your-server.database.windows.net", "SalesDB"),
    Navigation = Source{[Schema = SourceSchema, Item = "Transactions"]}[Data],
    FilteredRows = Table.SelectRows(Navigation, each [fiscal_month] = FiscalMonth)
in
    FilteredRows

Notice that SourceSchema and FiscalMonth are referenced directly by name — no quotes, no special syntax. In M, a parameter defined in the parameters pane is just a named value in the same scope as your query steps. When Power Query evaluates this expression, it substitutes the current value of each parameter.

Building More Complex Dynamic Logic

Parameters aren't just for simple equality filters. Once you understand that a parameter is just a value in scope, you can use it anywhere an M expression is valid — including as part of URL construction, date range calculation, or conditional logic.

Here's a more sophisticated example. You're pulling data from a REST API where the endpoint URL includes the fiscal month:

let
    BaseUrl = "https://api.yoursystem.com/v2/transactions",
    FullUrl = BaseUrl & "?month=" & FiscalMonth & "&schema=" & SourceSchema,
    Source = Json.Document(Web.Contents(FullUrl)),
    AsTable = Table.FromList(Source, Splitter.SplitByNothing()),
    Expanded = Table.ExpandRecordColumn(AsTable, "Column1", 
               {"transaction_id", "amount", "region", "fiscal_month"})
in
    Expanded

Or you might want to derive a date range from a single month parameter for use in a WHERE clause equivalent:

let
    // Parse FiscalMonth ("YYYY-MM") into a start and end date
    ParsedDate = Date.FromText(FiscalMonth & "-01"),
    StartDate = ParsedDate,
    EndDate = Date.EndOfMonth(ParsedDate),
    
    Source = Sql.Database("your-server.database.windows.net", "SalesDB"),
    Transactions = Source{[Schema = SourceSchema, Item = "Transactions"]}[Data],
    
    // Filter to only rows in the target month
    FilteredRows = Table.SelectRows(Transactions, 
                   each [transaction_date] >= StartDate and 
                        [transaction_date] <= EndDate)
in
    FilteredRows

This is where parameterization really starts earning its keep. The dataflow itself encodes the business logic — "extract one fiscal month of data from the specified schema" — while the pipeline controls which month and schema that means for any given run.

---

Configuring the Dataflow Activity in a Fabric Pipeline

With your parameterized dataflow saved and published, switch over to your Fabric data pipeline. If you don't have one yet, create a new pipeline in the same workspace.

First, define the pipeline's own parameters. In the pipeline canvas, click on an empty area to deselect all activities. In the bottom pane, you'll see a Parameters tab. Add a parameter:

• Name: p_fiscal_month
• Type: String
• Default value: 2024-01

Add another:

• Name: p_source_schema
• Type: String
• Default value: dbo

Now drag a Dataflow activity onto the canvas from the Activities panel. Connect it to whatever predecessor activity you need (a Lookup for watermark logic, a Set Variable activity, or directly as the first step).

Click the Dataflow activity to open its settings in the bottom pane. Under the Settings tab, select your parameterized Dataflow Gen2 from the Dataflow dropdown. Once selected, you'll see a Parameters section appear below — this is Fabric automatically discovering the parameters you defined in the Power Query editor.

For each discovered parameter, you'll see a field where you can enter a value. Instead of typing a static value, click the field and choose Add dynamic content (the link that appears when you focus the field, or the icon next to it). This opens the expression editor.

For the FiscalMonth parameter, enter this expression:

@pipeline().parameters.p_fiscal_month

For the SourceSchema parameter:

@pipeline().parameters.p_source_schema

Save the pipeline. At this point, you have the full stack: a Power Query parameter defined in the dataflow, a Dataflow activity that binds a pipeline expression to that parameter, and a pipeline parameter that receives its value from whoever triggers the pipeline.

---

Triggering the Pipeline with Specific Parameter Values

There are several ways a pipeline run can receive parameter values.

Manual trigger with parameters. When you click Run on a pipeline that has parameters defined, Fabric presents a dialog prompting you to enter values for each parameter. This is the simplest testing mechanism — use it to verify your end-to-end flow before automating anything.

Scheduled trigger. Schedule triggers in Fabric don't currently support dynamic parameter binding at the trigger level (unlike Azure Data Factory's tumbling window triggers). For scheduled runs with static parameter values, you'd either set the default parameter value at the pipeline level or use a preceding Set Variable or Lookup activity to derive the value dynamically within the pipeline.

Pipeline calling a pipeline. This is the most powerful pattern for parameterized dataflows. A parent orchestration pipeline can call your dataflow pipeline as an Execute Pipeline activity, passing specific parameter values:

@formatDateTime(addDays(utcNow(), -30), 'yyyy-MM')

The above expression calculates last month's fiscal month string dynamically from the current date. The parent pipeline can iterate through a list of months (using a ForEach activity) or compute the target period based on watermark data from a control table.

From a Lookup activity result. A common production pattern is to query a control table that tracks which periods have been processed, then pass the "next unprocessed period" as the parameter value:

@activity('GetNextPeriod').output.firstRow.fiscal_month

This connects your parameterized dataflow directly to an incremental loading strategy — exactly the kind of pattern described in Incrementally Loading Data into a Fabric Lakehouse with Watermarks and Pipeline Lookup Activities.

---

Building a Reusable Ingestion Pattern: The Multi-Region Example

Let's put this all together in a realistic scenario. You're building a medallion architecture where the bronze layer ingests raw data from regional sales systems. There are four regions: EMEA, APAC, AMER, and LATAM. Each region has the same schema but lives in a different SQL database. You want a single Dataflow Gen2 that handles all four regions.

Step 1: Define the Dataflow Parameters

In your Dataflow Gen2, define three parameters:

Name	Type	Default Value
Region	Text	EMEA
SourceServer	Text	emea-sql.database.windows.net
TargetTableSuffix	Text	emea

Step 2: Write the Parameterized Query

let
    // Connect to the region-specific server
    Source = Sql.Database(SourceServer, "SalesDB"),
    
    // Load the transactions table
    RawTransactions = Source{[Schema = "dbo", Item = "Transactions"]}[Data],
    
    // Add the region tag so we know where each row came from after ingestion
    WithRegion = Table.AddColumn(RawTransactions, "source_region", 
                 each Region, type text),
    
    // Standardize column names to snake_case
    Renamed = Table.RenameColumns(WithRegion, {
        {"TransactionID", "transaction_id"},
        {"TransactionDate", "transaction_date"},
        {"CustomerID", "customer_id"},
        {"SalesAmount", "sales_amount"},
        {"CurrencyCode", "currency_code"}
    }),
    
    // Cast data types explicitly
    Typed = Table.TransformColumnTypes(Renamed, {
        {"transaction_id", type text},
        {"transaction_date", type date},
        {"customer_id", type text},
        {"sales_amount", type number},
        {"currency_code", type text},
        {"source_region", type text}
    })
in
    Typed

Step 3: Configure the Output Destination

In the Dataflow Gen2 output configuration, set the destination to your bronze lakehouse. For the table name, you want it to vary by region. Unfortunately, destination table names aren't directly parameterizable through the UI — the table name in the destination settings is a static string.

This is a real limitation worth knowing about: you can parameterize the source query logic, but the output table name in the destination pane is fixed at design time.

There are two ways to handle this:

Option A: Write to a single table with the source_region column included (as the query above does). The pipeline then runs the same dataflow four times, once per region, all writing to the same bronze_transactions table. You rely on the source_region column to distinguish data and use upsert/append mode appropriately.

Option B: Use a Notebook activity after the Dataflow activity to rename or repartition the table by region, leveraging Transforming Data with Spark Notebooks in Microsoft Fabric: PySpark for Lakehouse Tables for that post-processing step.

For most bronze layer use cases, Option A is cleaner. Append to one table, keep all the raw data, and let the silver transformation split it out by region if needed.

Step 4: Build the Orchestration Pipeline

In your pipeline, define these parameters:

• p_region (String, default: EMEA)
• p_source_server (String, default: emea-sql.database.windows.net)

Add a Dataflow activity and bind the parameters:

• Region → @pipeline().parameters.p_region
• SourceServer → @pipeline().parameters.p_source_server
• TargetTableSuffix → @toLower(pipeline().parameters.p_region)

Step 5: Create the Parent Orchestration Pipeline

Create a second pipeline — the orchestrator. It has no parameters of its own. It contains a ForEach activity that iterates over a hardcoded (or lookup-driven) array of regions:

[
    {"region": "EMEA", "server": "emea-sql.database.windows.net"},
    {"region": "APAC", "server": "apac-sql.database.windows.net"},
    {"region": "AMER", "server": "amer-sql.database.windows.net"},
    {"region": "LATAM", "server": "latam-sql.database.windows.net"}
]

Inside the ForEach, place an Execute Pipeline activity that calls your dataflow pipeline with these parameter values:

• p_region → @item().region
• p_source_server → @item().server

Set the ForEach to run sequentially (isSequential: true) to avoid overwhelming your SQL sources, or set it to parallel with a batchCount of 2 if your capacity handles concurrent dataflow runs.

The resulting data lands in your bronze lakehouse, tagged with source_region, ready for silver-layer transformations.

---

Hands-On Exercise

Build a complete parameterized ingestion flow using public data. Here's a self-contained exercise you can complete in a trial workspace.

Scenario: You need to ingest country-level COVID-19 data from the Our World in Data REST API, parameterized so the pipeline controls which country code to fetch.

Step 1: Create the parameterized Dataflow Gen2.

Open a new Dataflow Gen2. Create two parameters:

• CountryCode (Text, default: GBR)
• MetricName (Text, default: new_cases)

Create a query called CovidMetrics with this M code:

let
    // Build the API URL from parameters
    BaseUrl = "https://covid.ourworldindata.org/data/owid-covid-data.json",
    
    // Load the full JSON
    RawJson = Json.Document(Web.Contents(BaseUrl)),
    
    // Extract the specific country using the CountryCode parameter
    CountryData = Record.Field(RawJson, CountryCode),
    
    // Get the "data" array which contains daily records
    DataArray = CountryData[data],
    
    // Convert to table
    AsTable = Table.FromList(DataArray, Splitter.SplitByNothing()),
    
    // Expand the record column
    Expanded = Table.ExpandRecordColumn(AsTable, "Column1",
               {"date", "new_cases", "new_deaths", "total_cases", 
                "total_deaths", "people_vaccinated"}),
    
    // Add the country code for traceability
    WithCountry = Table.AddColumn(Expanded, "country_code", 
                  each CountryCode, type text),
    
    // Filter to just the requested metric (keep date, country, and the metric)
    Selected = Table.SelectColumns(WithCountry, 
               {"date", "country_code", MetricName}),
    
    // Type the date column
    Typed = Table.TransformColumnTypes(Selected, {{"date", type date}})
in
    Typed

Set the output destination to a table called covid_metrics in your bronze lakehouse, using append mode.

Step 2: Test the dataflow in isolation.

Click Publish, then run the dataflow manually. With default parameters (GBR, new_cases), it should append UK new cases data to your table. Check the lakehouse to confirm rows landed.

Step 3: Create the pipeline.

Create a pipeline with parameters:

• p_country_code (String, default: GBR)
• p_metric_name (String, default: new_cases)

Add a Dataflow activity bound to your dataflow, with:

• CountryCode → @pipeline().parameters.p_country_code
• MetricName → @pipeline().parameters.p_metric_name

Step 4: Run with multiple country codes.

Trigger the pipeline manually three times with these parameter combinations:

• USA, new_cases
• DEU, new_deaths
• JPN, total_vaccinations

After each run, query the covid_metrics table in your lakehouse SQL endpoint to verify that rows from each country are present and the country_code column correctly identifies the source.

Step 5: Build the ForEach orchestrator.

Create a parent pipeline with a ForEach activity iterating over:

[
    {"country": "USA", "metric": "new_cases"},
    {"country": "DEU", "metric": "new_cases"},
    {"country": "JPN", "metric": "new_cases"},
    {"country": "GBR", "metric": "new_cases"}
]

Inside the ForEach, use an Execute Pipeline activity calling your dataflow pipeline with @item().country and @item().metric. Run the orchestrator pipeline once and verify that data for all four countries lands in the table from a single pipeline run.

---

Common Mistakes & Troubleshooting

The Parameter Doesn't Appear in the Dataflow Activity Settings

Symptom: You've defined parameters in Power Query, but when you select your dataflow in the pipeline's Dataflow activity, no parameter fields appear.

Cause: The dataflow must be published (not just saved) for the pipeline to discover its parameters. Power Query parameters are surfaced at publish time.

Fix: Go back to your dataflow, make any minor change (even a comment), publish it, then return to the pipeline and re-select the dataflow. The parameters should now appear.

Parameter Value Arrives as Null at Runtime

Symptom: The pipeline runs, the dataflow activity succeeds, but the data shows default parameter values instead of the injected ones.

Cause: The dynamic content expression in the Dataflow activity settings contains a typo, or the pipeline parameter name doesn't match what the expression references.

Fix: In the Dataflow activity settings, click the expression field and verify the expression resolves correctly. Use the Debug run feature in the pipeline to step through execution and inspect what value each expression produces at runtime.

Type Mismatch Errors in the Query

Symptom: The dataflow errors with a message like "Cannot convert value '2024-01' to type Date" or a column comparison fails.

Cause: Power Query parameters are typed at definition time, but Power Query's type system is stricter than you might expect. A Text parameter used in a date filter will fail unless you explicitly convert it.

Fix: Wrap parameter references in explicit conversion functions:

// Wrong - comparing text to date column
Table.SelectRows(Source, each [transaction_date] = FiscalMonth)

// Right - convert the parameter to the correct type first
let
    TargetDate = Date.FromText(FiscalMonth & "-01"),
    Filtered = Table.SelectRows(Source, 
               each [transaction_date] >= TargetDate and 
                    [transaction_date] <= Date.EndOfMonth(TargetDate))
in
    Filtered

Dataflow Activity Fails with "Parameter Not Found"

Symptom: The pipeline errors on the Dataflow activity with a message indicating a parameter name wasn't found.

Cause: The parameter name in the activity settings doesn't exactly match the name defined in the Power Query editor. Parameter names are case-sensitive in this binding.

Fix: Open the Manage Parameters dialog in your dataflow and copy the exact parameter name. Paste it into the activity settings. If you renamed a parameter in Power Query, re-publish the dataflow and refresh the parameter binding in the pipeline activity.

ForEach Runs Cause Data Duplication

Symptom: After running a ForEach that iterates over multiple parameter combinations, you see duplicate rows in the destination table.

Cause: You're running the same combination twice (perhaps during testing), and the destination is in append mode with no deduplication.

Fix: Either use upsert mode in the destination (if your lakehouse table has a defined key), or add a preceding pipeline step that deletes/truncates data for the specific parameter combination before appending. Alternatively, design the Power Query query to add a unique composite key column that a subsequent Spark notebook can deduplicate.

Parameters Work Locally but Fail in Pipeline Runs

Symptom: Running the dataflow manually with default parameters works fine. Running it through the pipeline with different parameter values fails.

Cause: Often a credential or source access issue. Your default parameter points to a dev/test server you have access to; the pipeline-injected parameter points to a prod server where the dataflow's connection credentials aren't configured.

Fix: In the Fabric portal, open the dataflow and go to Settings (the gear icon for the dataflow item, not the editor). Check which credentials are associated with each data source connection. If the pipeline is passing a different server name than the one the credentials were configured for, you may need to configure additional credentials for the parameterized connection string.

---

Summary & Next Steps

You've built something genuinely useful here. A parameterized Dataflow Gen2 — one that accepts runtime values from a pipeline — transforms what would otherwise be a collection of near-identical dataflows into a single, maintainable asset. When the schema changes, you fix one dataflow. When you add a new region or time period, you update the pipeline configuration, not the transformation logic.

The core mechanics to carry forward:

• Power Query parameters are defined in the Manage Parameters dialog and referenced by name in M expressions with no special syntax
• Type conversion is your responsibility — always explicitly cast parameter values to the target type before using them in comparisons or URL construction
• Dataflow Gen2 must be published before its parameters appear in pipeline activity settings
• Pipeline parameter expressions follow the @pipeline().parameters.ParameterName syntax and are injected at runtime through the Dataflow activity's parameter binding
• The ForEach + Execute Pipeline pattern is how you scale a parameterized dataflow across multiple contexts in a single orchestrated run

From here, there are several directions worth exploring. If your ingestion needs to be incremental rather than full-month loads, the watermark pattern in Incrementally Loading Data into a Fabric Lakehouse with Watermarks and Pipeline Lookup Activities extends naturally from what you've built. The parameters you've learned to pass can carry high-water mark values just as easily as region codes or month strings.

If you're writing to a lakehouse and need to optimize the resulting Delta tables for downstream reporting performance, Optimizing Delta Table Performance in a Fabric Lakehouse: V-Order, OPTIMIZE, VACUUM, and Z-Order for Faster Queries and Smaller Storage covers exactly that. And once your bronze data is clean and structured, Connecting a Power BI Semantic Model to a Fabric Lakehouse in Direct Lake Mode: Creating, Refreshing, and Optimizing Delta Tables for Reporting shows you how to surface it in Power BI without importing a copy.

The parameterization pattern you've learned today is the foundation of scalable, maintainable ingestion in Fabric. Use it consistently and you'll spend your time building new capabilities rather than patching duplicate dataflows.