Power Query – Automation and Parameters: Difference between revisions

From Training Material
Jump to navigation Jump to search
← Older editNewer edit →
No edit summary
Line 2: Line 2:


== Objective ==
== Objective ==
In this module, you'll learn how to automate tasks using query parameters and load data dynamically.
Use parameters to automate queries and build dynamic data loading processes.


== Files Used ==
== Files Used ==
* [[Media:ExchangeRates.xlsx|Download ExchangeRates.xlsx]]
* [[Media:ExchangeRates.xlsx|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 ==
== Creating a Parameter ==
'''Steps:'''
'''Steps:'''
# In Power Query Editor, go to the '''Home''' tab → '''Manage Parameters''' → '''New Parameter'''
# In Power Query Editor → '''Home''' tab → '''Manage Parameters''' → '''New Parameter'''
# Fill in the fields:
# Fill in:
#* '''Name''': e.g. `TargetCurrency`
#* '''Name''': e.g. `TargetCurrency`
#* '''Type''': Text
#* '''Type''': Text
#* '''Suggested Values''': Any value (or a list, if preferred)
#* '''Current Value''': e.g. `EUR`
#* '''Current Value''': e.g. `EUR`
# Click '''OK'''
# Click '''OK'''


== Using a Parameter in a Filter ==
== Using a Parameter in Filtering ==
'''Steps:'''
'''Steps:'''
# Load '''ExchangeRates.xlsx''' into Power Query
# Load '''ExchangeRates.xlsx''' into Power Query
# Select the column '''Currency'''
# Select the column '''Currency'''
# Go to the '''Home''' tab → '''Keep Rows''' → '''Keep Rows Where...'''
# Go to '''Home''' tab → '''Keep Rows''' → '''Keep Rows Where...'''
# Choose: Column ''Currency'' equals parameter ''TargetCurrency''
# Set: ''Currency'' equals ''Parameter'' → choose `TargetCurrency`


== Using a Parameter in M Code ==
== Using Parameters in M Code ==
You can reference parameters directly in the formula bar or Advanced Editor. Example:
You can reference parameters directly in formulas:


<syntaxhighlight lang="m">
= Table.SelectRows(Source, each [Currency] = TargetCurrency)
</syntaxhighlight>


= Table.SelectRows(Source, each [Currency] = TargetCurrency)
== Parameter for File Path ==
 
'''Scenario:''' Create a reusable query for loading Excel files.
== Using Parameters for File Paths ==
'''Scenario:''' You want to dynamically load a file based on a path stored as a parameter.


'''Steps:'''
'''Steps:'''
# Create a Text parameter, e.g. `FilePath`
# Create a parameter `FilePath` (Type: Text)
# Go to the '''Home''' tab → '''New Source''' → '''Excel Workbook'''
# Go to '''Home''' tab → '''New Source''' → '''Excel Workbook'''
# In the file dialog, use the value from the parameter
# In file path dialog, paste the value from the parameter
# If needed, open the Advanced Editor and replace static path with `FilePath`
# Or: go to Advanced Editor and replace path with `FilePath`


== Automating Folder Imports ==
== Automating Folder Imports ==
'''Steps:'''
'''Steps:'''
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Folder'''
# Go to '''Data''' tab → '''Get Data''' → '''From File''' → '''From Folder'''
# Select a folder containing multiple files (e.g. sales reports)
# Select a folder (e.g. with sales data)
# Click '''Transform Data'''
# Click '''Transform Data'''
# Power Query will open a preview — click '''Combine''' → '''Combine & Transform Data'''
# In Power Query Editor, click '''Combine''' → '''Combine & Transform Data'''
# Use a parameter for the folder path if needed (replace in Advanced Editor or via formula bar)
# 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:'''
# Load '''ExchangeRates.xlsx'''
# Create a `TargetCurrency` parameter (e.g. "USD")
# Use it to filter the rows
# Load the result into a worksheet


== Extension: Combine Parameters and Folder Query ==
'''Goal:''' Allow the user to set a folder path dynamically.


→ Continue to [[Power Query – Power Pivot Overview|Module 5: Power Pivot Overview]]
'''Steps:'''
# Create parameter `FolderPath`
# Use it in '''From Folder''' source
# Refresh the query after changing parameter value
# Load combined data to Excel sheet


----


→ Continue to [[Power Query – Power Pivot Overview|Module 5: Power Pivot Overview]]


Return to [[Power_Query_–_Basic|Main Page]]
Return to [[Power_Query_–_Basic|Main Page]]

Revision as of 09:47, 19 June 2025

Module 4: Automation and Parameters

Objective

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

Files Used

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

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