← Back to Chapters

Power Query Editor – Parameters

? Power Query Editor – Parameters

✨ Quick Overview

Parameters act like dynamic inputs that control how Power Query loads and filters data. Instead of hardcoding values, parameters allow flexible and reusable solutions.

? Key Concepts

  • Parameters are global variables in Power Query
  • They control filtering, logic, and data sources
  • One parameter can affect multiple queries
  • Commonly used in enterprise-level reports

? Syntax / Theory

Parameter → Stores value → Used inside filter or logic → Refresh updates results

? Sample Practice Data

? View Code Example 
// Sample data for parameter-based filtering
OrderID,OrderDate,City,Amount
1,2024-01-05,Pune,15000
2,2024-02-10,Mumbai,45000
3,2024-03-18,Delhi,5000
4,2024-04-25,Pune,20000
5,2024-05-30,Mumbai,8000

? Where to Create Parameters

  1. Power BI Desktop → Transform Data
  2. Power Query Editor → Home
  3. Manage Parameters → New Parameter

? Parameter Types

  • Text Parameter
  • Date Parameter
  • True / False (Toggle) Parameter

? Text Parameter Example

? View Code Example 
// Filter rows using text parameter
[City] = pCity

? Toggle Parameter Example

? View Code Example 
// Enable or disable high-value filter
if pShowHighValue = true then [Amount] > 10000 else true

? Date Parameter Example

? View Code Example 
// Filter records from selected start date
[OrderDate] >= pStartDate

? Use Cases

  • Dynamic date filtering: Load only the last 30 days of data based on a user-defined date.
  • Regional reports: Build one report and switch between "Pune", "Mumbai", or "Delhi" using a parameter.
  • Incremental refresh: Parameters like RangeStart and RangeEnd are required for high-volume data refresh.
  • Environment Switching: Toggle between Dev, Test, and Prod Database strings without rewriting queries.

⚡ Interactive Parameter Simulator

Change parameter values to see dynamic filtering.

Adjust parameters above to see results.

? Tips & Best Practices

  • Naming Convention: Prefix your parameters with 'p' (e.g., pCity, pStartDate) to distinguish them from column names.
  • Fixed Lists: Use "List of values" in the Parameter setup to prevent users from typing invalid entries.
  • Model Performance: Use parameters to filter data at the source (Query Folding) to keep the model fast.
Use parameters instead of hardcoded values to build scalable and professional reports.

? Try It Yourself

Create a Text parameter → Apply city filter → Add date parameter → Add toggle logic