Power Query – Automation and Parameters
Module 4: Automation and Parameters
Objective
Use parameters to automate queries and build dynamic data loading processes.
Files Used
Creating a Parameter
Steps:
- In Power Query Editor → Home tab → Manage Parameters → New Parameter
- Fill in:
- Name: e.g. `TargetCurrency`
- Type: Text
- Current Value: e.g. `EUR`
- Click OK
Using a Parameter in Filtering
Steps:
- Load ExchangeRates.xlsx into Power Query
- Select the column Currency
- Go to Home tab → Keep Rows → Keep Rows Where...
- Set: Currency equals Parameter → choose `TargetCurrency`
Using Parameters in M Code
You can reference parameters directly in formulas:
= Table.SelectRows(Source, each [Currency] = TargetCurrency)Parameter for File Path
Scenario: Create a reusable query for loading Excel files.
Steps:
- Create a parameter `FilePath` (Type: Text)
- Go to Home tab → New Source → Excel Workbook
- In file path dialog, paste the value from the parameter
- Or: go to Advanced Editor and replace path with `FilePath`
Automating Folder Imports
Steps:
- Go to Data tab → Get Data → From File → From Folder
- Select a folder (e.g. with sales data)
- Click Transform Data
- In Power Query Editor, click Combine → Combine & Transform Data
- Use a parameter for folder path if needed
Additional Exercise: Parameterized Currency Filter
Goal: Build a flexible report that shows exchange rates for one currency.
Steps:
- Load ExchangeRates.xlsx
- Create a `TargetCurrency` parameter (e.g. "USD")
- Use it to filter the rows
- Load the result into a worksheet
Extension: Combine Parameters and Folder Query
Goal: Allow the user to set a folder path dynamically.
Steps:
- Create parameter `FolderPath`
- Use it in From Folder source
- Refresh the query after changing parameter value
- Load combined data to Excel sheet
→ Continue to Module 5: Power Pivot Overview
Return to Main Page
