Power Query – Automation and Parameters

From Training Material
Jump to navigation Jump to search

Module 4: Automation and Parameters

Objective

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

Files Used

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 ExchangeRates.xlsx
  3. In the Navigator → select the worksheet → click Transform Data
  4. In Power Query Editor:
    • Select the column named `Currency`
    • Go to the Home tab → click Keep Rows → choose Keep Rows Where...
  5. In the dialog:
    • Select the column `Currency`
    • Condition: equals
    • Select the value type as: 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

Retrieved from "https://training-course-material.com/index.php?title=Power_Query_–_Automation_and_Parameters&oldid=88918"