Power Query – Automation and Parameters

Module 4: Automation and Parameters

Objective

In this module, you'll learn how to automate tasks using query parameters and load data dynamically.

Files Used

• Download ExchangeRates.xlsx

What Are Parameters?

Parameters let you store a value (like a currency code or file path) and reuse it in queries. They’re useful for automating imports and applying dynamic filters.

Creating a Parameter

Steps:

1. In Power Query Editor, go to the Home tab → Manage Parameters → New Parameter
2. Fill in the fields:
   • Name: e.g. `TargetCurrency`
   • Type: Text
   • Suggested Values: Any value (or a list, if preferred)
   • Current Value: e.g. `EUR`
3. Click OK

Using a Parameter in a Filter

Steps:

1. Load ExchangeRates.xlsx into Power Query
2. Select the column Currency
3. Go to the Home tab → Keep Rows → Keep Rows Where...
4. Choose: Column Currency equals parameter TargetCurrency

Using a Parameter in M Code

You can reference parameters directly in the formula bar or Advanced Editor. Example:

= Table.SelectRows(Source, each [Currency] = TargetCurrency)

Using Parameters for File Paths

Scenario: You want to dynamically load a file based on a path stored as a parameter.

Steps:

1. Create a Text parameter, e.g. `FilePath`
2. Go to the Home tab → New Source → Excel Workbook
3. In the file dialog, use the value from the parameter
4. If needed, open the Advanced Editor and replace static path with `FilePath`

Automating Folder Imports

Steps:

1. Go to the Data tab → Get Data → From File → From Folder
2. Select a folder containing multiple files (e.g. sales reports)
3. Click Transform Data
4. Power Query will open a preview — click Combine → Combine & Transform Data
5. Use a parameter for the folder path if needed (replace in Advanced Editor or via formula bar)

---

→ 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