Power Query – Automation and Parameters - Revision history

Gpruszczynski1: /* Extension: Combine Parameters and Folder Query */

2025-06-22T12:44:21Z

Extension: Combine Parameters and Folder Query

← Older revision		Revision as of 12:44, 22 June 2025
Line 97:		Line 97:
	= Folder.Files(FolderPath)		= Folder.Files(FolderPath)

	~~== Extension: Combine Parameters and Folder Query ==~~
	~~'''Goal:''' Allow user to control which folder is imported dynamically.~~

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

	~~= Folder.Files(FolderPath)~~

	~~# Power Query will combine the files~~
	~~# Load the final result to Excel via '''Close & Load'''~~

	----		----

Gpruszczynski1: /* Additional Exercise: Parameterized Currency Filter */

2025-06-22T12:43:36Z

Additional Exercise: Parameterized Currency Filter

← Older revision		Revision as of 12:43, 22 June 2025
Line 96:		Line 96:

	= Folder.Files(FolderPath)		= Folder.Files(FolderPath)

	~~== Additional Exercise: Parameterized Currency Filter ==~~
	~~'''Goal:''' Build a flexible report showing exchange rates for one selected currency.~~

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

	== Extension: Combine Parameters and Folder Query ==		== Extension: Combine Parameters and Folder Query ==

Gpruszczynski1: /* Extension: Combine Parameters and Folder Query */

2025-06-22T12:05:28Z

Extension: Combine Parameters and Folder Query

← Older revision		Revision as of 12:05, 22 June 2025
Line 122:		Line 122:
	#* In the folder path dialog → paste the value from `FolderPath`		#* In the folder path dialog → paste the value from `FolderPath`
	# OR in Advanced Editor, replace the path with:		# OR in Advanced Editor, replace the path with:
	~~<syntaxhighlight lang="m">~~
	= Folder.Files(FolderPath)		= Folder.Files(FolderPath)
	~~</syntaxhighlight>~~
	# Power Query will combine the files		# Power Query will combine the files
	# Load the final result to Excel via '''Close & Load'''		# Load the final result to Excel via '''Close & Load'''

Gpruszczynski1: /* Automating Folder Imports */

2025-06-22T12:05:02Z

Automating Folder Imports

← Older revision		Revision as of 12:05, 22 June 2025
Line 94:		Line 94:
	# Replace the folder path with `FolderPath`		# Replace the folder path with `FolderPath`
	# OR go to Advanced Editor and replace:		# OR go to Advanced Editor and replace:
	~~<syntaxhighlight lang="m">~~
	= Folder.Files(FolderPath)		= Folder.Files(FolderPath)
	~~</syntaxhighlight>~~

	== Additional Exercise: Parameterized Currency Filter ==		== Additional Exercise: Parameterized Currency Filter ==

Gpruszczynski1: /* Parameter for File Path */

2025-06-22T12:03:47Z

Parameter for File Path

← Older revision		Revision as of 12:03, 22 June 2025
Line 75:		Line 75:
	#* Instead of browsing, paste the value from the `FilePath` parameter directly		#* Instead of browsing, paste the value from the `FilePath` parameter directly
	# OR: go to '''Advanced Editor''' and replace the hardcoded path:		# OR: go to '''Advanced Editor''' and replace the hardcoded path:
	~~<syntaxhighlight lang="m">~~
	= Excel.Workbook(File.Contents(FilePath), null, true)		= Excel.Workbook(File.Contents(FilePath), null, true)
	~~</syntaxhighlight>~~

	== Automating Folder Imports ==		== Automating Folder Imports ==

Gpruszczynski1: /* Using Parameters in M Code */

2025-06-22T12:03:03Z

Using Parameters in M Code

@@ Line 59: / Line 59: @@
 '''Result:'''
 Whenever the value in the Excel cell is changed (e.g. from `USD` to `EUR`), clicking **Refresh All** will automatically update the result.
 == Parameter for File Path ==

Gpruszczynski1: /* Using Parameters in M Code */

2025-06-22T12:02:44Z

Using Parameters in M Code

← Older revision		Revision as of 12:02, 22 June 2025
Line 67:		Line 67:
	# Click on the filtering step in the Applied Steps pane		# Click on the filtering step in the Applied Steps pane
	# Go to the formula bar and ensure the logic looks like:		# Go to the formula bar and ensure the logic looks like:
	~~<syntaxhighlight lang="m">~~
	= Table.SelectRows(Source, each [Currency] = TargetCurrency)		= Table.SelectRows(Source, each [Currency] = TargetCurrency)
	~~</syntaxhighlight>~~
	This will dynamically filter based on the parameter value.		This will dynamically filter based on the parameter value.

Gpruszczynski1: /* Using a Parameter in Filtering */

2025-06-22T11:56:30Z

Using a Parameter in Filtering

← Older revision		Revision as of 11:56, 22 June 2025
Line 23:		Line 23:

	== Using a Parameter in Filtering ==		== Using a Parameter in Filtering ==
	'''Goal:''' ~~Filter~~ a ~~column dynamically using a parameter~~.		'''Goal:''' Allow the user to control the filtered currency by typing a value directly in an Excel cell.

	'''Steps:'''		'''Steps:'''
	# In Excel ~~→ go to~~ the '''~~Data~~''' ~~tab → click~~ '''~~Get Data~~''' → '''~~From File~~''' → '''~~From Workbook~~'''		# In Excel:
	~~# Select and load the file:~~ '''~~ExchangeRates.xlsx'''~~		#* Type a value like `USD` in cell `A2`
	~~# In the Navigator window:~~		#* In cell `A1`, enter the header name: `Currency`
	~~#* Select the worksheet with the exchange rate data~~		#* Select cells `A1:A2` and press '''Ctrl+T''' to create a table
	~~#* Click~~ '''~~Transform Data''' to open the Power Query Editor~~		#* Name the table (e.g. `ParameterTable`) via the '''Table Design''' tab
			# Go to the '''Data''' tab → click '''Get Data''' → '''From Table/Range'''
	# In Power Query Editor:		# In Power Query Editor:
	#* ~~Locate~~ the column ~~named~~ `Currency`		#* Ensure the table has one column (e.g. `Currency`) and one row (e.g. `USD`)
	~~#* Click the filter icon~~ (~~▾) in the header of the~~ `~~Currency~~` ~~column~~		#* Right-click on the `USD` value → select '''Drill Down'''
	#* ~~From~~ the ~~dropdown menu~~ → select '''~~Text Filters~~''' ~~→ then choose~~ '''~~Equals~~...'''		# This will create a query returning just the single value
	# In ~~the Filter Rows dialog~~:		# Click '''Home → Close & Load To...''' → choose '''Only Create Connection'''
	#* On the ~~right side of~~ the ~~comparison~~ value ~~field → click the drop-down arrow~~		# Rename the query to: `CurrencyParameter`
	#* ~~Select~~ '''~~Parameter~~'''
	~~#* Choose the parameter~~: ~~`TargetCurrency`~~		# Next, load the main dataset (e.g. ExchangeRates.xlsx)
	# Click '''OK'''		# 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)

			# 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 ==		== Using Parameters in M Code ==

Gpruszczynski1: /* Using a Parameter in Filtering */

2025-06-22T10:52:23Z

Using a Parameter in Filtering

← Older revision		Revision as of 10:52, 22 June 2025
Line 27:		Line 27:
	'''Steps:'''		'''Steps:'''
	# In Excel → go to the '''Data''' tab → click '''Get Data''' → '''From File''' → '''From Workbook'''		# In Excel → go to the '''Data''' tab → click '''Get Data''' → '''From File''' → '''From Workbook'''
	# Select and load '''ExchangeRates.xlsx'''		# Select and load the file: '''ExchangeRates.xlsx'''
	# In the Navigator ~~→ select~~ the worksheet ~~→ click~~ '''Transform Data'''		# In the Navigator window:
			#* Select the worksheet with the exchange rate data
			#* Click '''Transform Data''' to open the Power Query Editor
	# In Power Query Editor:		# In Power Query Editor:
	#* ~~Select~~ the column named `Currency`		#* Locate the column named `Currency`
	#* ~~Go to~~ the ~~'''Home''' tab~~ → ~~click~~ '''~~Keep Rows~~''' → choose '''~~Keep Rows Where~~...'''		#* Click the filter icon (▾) in the header of the `Currency` column
	# In the dialog:		#* From the dropdown menu → select '''Text Filters''' → then choose '''Equals...'''
	#* ~~Select~~ the ~~column `Currency`~~		# In the Filter Rows dialog:
	~~#* Condition: equals~~		#* On the right side of the comparison value field → click the drop-down arrow
	#* Select ~~the value type as:~~ '''Parameter'''		#* Select '''Parameter'''
	#* Choose the parameter: `TargetCurrency`		#* Choose the parameter: `TargetCurrency`
	# Click '''OK'''		# Click '''OK'''

Gpruszczynski1 at 10:43, 22 June 2025

2025-06-22T10:43:20Z

Show changes