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: Allow the user to control the filtered currency by typing a value directly in an Excel cell.

Steps:

1. In Excel:
   • Type a value like `USD` in cell `A2`
   • In cell `A1`, enter the header name: `Currency`
   • Select cells `A1:A2` and press Ctrl+T to create a table
   • Name the table (e.g. `ParameterTable`) via the Table Design tab
2. Go to the Data tab → click Get Data → From Table/Range
3. In Power Query Editor:
   • Ensure the table has one column (e.g. `Currency`) and one row (e.g. `USD`)
   • Right-click on the `USD` value → select Drill Down
4. This will create a query returning just the single value
5. Click Home → Close & Load To... → choose Only Create Connection
6. Rename the query to: `CurrencyParameter`

1. Next, load the main dataset (e.g. ExchangeRates.xlsx)
2. In Power Query Editor:
   • Select the `Currency` column
   • Click the filter icon → select a sample value like `USD`
   • In the formula bar, replace the hardcoded value with your parameter query

Example: Replace:

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

With:

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

1. Click Close & Load to return the filtered data to Excel

Result: Whenever the value in the Excel cell is changed (e.g. from `USD` to `EUR`), clicking **Refresh All** will automatically update the result.

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