Power Query – Automation and Parameters

Module 4: Automation and Parameters

Objective

Use parameters to automate queries and build dynamic data loading processes.

Files Used

• Download ExchangeRates.xlsx

Creating a Parameter

Steps:

1. In Power Query Editor → Home tab → Manage Parameters → New Parameter
2. Fill in:
   • Name: e.g. `TargetCurrency`
   • Type: Text
   • Current Value: e.g. `EUR`
3. Click OK

Using a Parameter in Filtering

Steps:

1. Load ExchangeRates.xlsx into Power Query
2. Select the column Currency
3. Go to Home tab → Keep Rows → Keep Rows Where...
4. 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:

1. Create a parameter `FilePath` (Type: Text)
2. Go to Home tab → New Source → Excel Workbook
3. In file path dialog, paste the value from the parameter
4. Or: go to Advanced Editor and replace path with `FilePath`

Automating Folder Imports

Steps:

1. Go to Data tab → Get Data → From File → From Folder
2. Select a folder (e.g. with sales data)
3. Click Transform Data
4. In Power Query Editor, click Combine → Combine & Transform Data
5. 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:

1. Load ExchangeRates.xlsx
2. Create a `TargetCurrency` parameter (e.g. "USD")
3. Use it to filter the rows
4. Load the result into a worksheet

Extension: Combine Parameters and Folder Query

Goal: Allow the user to set a folder path dynamically.

Steps:

1. Create parameter `FolderPath`
2. Use it in From Folder source
3. Refresh the query after changing parameter value
4. Load combined data to Excel sheet

---

→ Continue to Module 5: Power Pivot Overview

Return to Main Page

• Module 1: Introduction to Power Query
• Module 2: Importing and Managing Data
• Module 3: Data Transformation Techniques
• Module 4: Automation and Parameters
• Module 5: Power Pivot Overview
• Module 6: Summary and Final Exercises