Power Query – Automation and Parameters

Module 4: Automation and Parameters

Objective

Use parameters to automate queries and build dynamic, reusable data loading processes in Power Query.

Files Used

• Download ExchangeRates.xlsx

Creating a Parameter

Goal: Create a user-defined value that can be reused throughout queries.

Steps:

1. Open Power Query Editor
2. In the ribbon → go to the Home tab → click Manage Parameters → select New Parameter
3. In the New Parameter dialog:
   • Name: `TargetCurrency`
   • Description: (optional)
   • Required: Checked
   • Type: Text
   • Suggested Values: Any value
   • Current Value: `EUR` (or any other currency from the dataset)
4. Click OK

Using a Parameter in Filtering

Goal: Filter a column dynamically using a parameter.

Steps:

1. In Excel → go to the Data tab → click Get Data → From File → From Workbook
2. Select and load the file: ExchangeRates.xlsx
3. In the Navigator window:
   • Select the worksheet with the exchange rate data
   • Click Transform Data to open the Power Query Editor
4. In Power Query Editor:
   • Locate the column named `Currency`
   • Click the filter icon (▾) in the header of the `Currency` column
   • From the dropdown menu → select Text Filters → then choose Equals...
5. In the Filter Rows dialog:
   • On the right side of the comparison value field → click the drop-down arrow
   • Select Parameter
   • Choose the parameter: `TargetCurrency`
6. Click OK

Using Parameters in M Code

Goal: Apply parameter logic manually in the formula bar or Advanced Editor.

Steps:

1. Make sure the parameter `TargetCurrency` exists
2. Click on the filtering step in the Applied Steps pane
3. Go to the formula bar and ensure the logic looks like:

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

This will dynamically filter based on the parameter value.

Parameter for File Path

Scenario: Allow the user to select the file path dynamically instead of hardcoding it.

Steps:

1. In Power Query Editor → go to Home tab → click Manage Parameters → select New Parameter
2. In the New Parameter dialog:
   • Name: `FilePath`
   • Type: Text
   • Current Value: Paste the full path to an Excel file (e.g. C:\\Data\\Sales_Q1.xlsx)
3. Click OK
4. Then, to load the file using this path:
5. Go to Home tab → click New Source → choose Excel Workbook
6. When prompted for the file path:
   • Instead of browsing, paste the value from the `FilePath` parameter directly
7. OR: go to Advanced Editor and replace the hardcoded path:

= Excel.Workbook(File.Contents(FilePath), null, true)

Automating Folder Imports

Goal: Use folder-based queries to dynamically import and combine files.

Steps:

1. In Excel → go to the Data tab → click Get Data → From File → From Folder
2. In the folder selection dialog → paste or browse to a folder with multiple Excel/CSV files
3. Click OK and then Transform Data
4. In Power Query Editor → you’ll see a table with metadata for each file
5. Click the button in the Content column → select Combine → choose Combine & Transform Data
6. Power Query creates a function and imports a sample file structure

Optional: Use parameter for folder path

1. Create a parameter named `FolderPath` (type: Text)
2. In the source step → click the gear icon on Source in the Applied Steps
3. Replace the folder path with `FolderPath`
4. OR go to Advanced Editor and replace:

= Folder.Files(FolderPath)

Additional Exercise: Parameterized Currency Filter

Goal: Build a flexible report showing exchange rates for one selected currency.

Steps:

1. Load ExchangeRates.xlsx into Power Query
2. Go to Home → Manage Parameters → New Parameter
   • Name: `TargetCurrency`
   • Type: Text
   • Current Value: e.g. `USD`
3. Apply a filter to the `Currency` column using this parameter:
   • Select `Currency` → Home → Keep Rows → Keep Rows Where...
   • Condition: equals parameter `TargetCurrency`
4. Load the result:
   • Go to Home → Close & Load To... → select output destination (table, worksheet, etc.)

Extension: Combine Parameters and Folder Query

Goal: Allow user to control which folder is imported dynamically.

Steps:

1. Create a parameter named `FolderPath` (type: Text)
2. Set its value to the full folder location where your files are stored
3. In Power Query:
   • Go to Data tab → Get Data → From File → From Folder
   • In the folder path dialog → paste the value from `FolderPath`
4. OR in Advanced Editor, replace the path with:

= Folder.Files(FolderPath)

1. Power Query will combine the files
2. Load the final result to Excel via Close & Load

---

→ 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