Automation in Power Query

Everything you have built in Power Query so far — the cleaning steps, the merges, the transformations — runs again automatically every time you refresh. That is the whole point. But automation in Power Query goes further than a single Refresh button. This lesson covers how to refresh on demand and on schedule, how to connect to files and folders that keep updating, how to combine all files in a folder into one query automatically, and how to use parameters to make queries dynamic and reusable without editing the M code directly.

Refresh — On Demand and Automatic

Every query connected to an external source can be refreshed to pull in the latest data. The most basic form is a manual refresh — you click a button and Power Query reruns all the steps against the current state of the source.

For most reports shared with colleagues, the most useful setting is "Refresh data when opening the file." This means anyone who opens the workbook automatically gets the latest data without needing to know where the Refresh button is. Combine this with a shared network folder or SharePoint source and you have a completely hands-off refresh cycle.

Connecting to a Folder — Combine All Files Automatically

One of the most powerful automation features in Power Query is the ability to connect to a folder rather than a single file. When you connect to a folder, Power Query finds every file in that folder, applies your transformation steps to each one, and combines them all into a single table. Drop a new monthly file into the folder, hit Refresh, and it is automatically included.

How to connect to a folder:
  Data → Get Data → From File → From Folder
  Browse to the folder → OK

Power Query shows a list of all files found.
Click "Combine and Transform Data" to merge all files and open the editor.

Power Query automatically:
  - Creates a helper query with the sample file transformations
  - Creates a function query that applies those steps to each file
  - Creates the main query that calls the function for every file in the folder

To filter which files are included (e.g. only .xlsx, not .csv):
  In the main query → filter the Extension column to ".xlsx"
  Or filter the Name column to files starting with "Sales_"

The folder connection pattern is the gold standard for recurring monthly or weekly reports. Set up the transformation once on a sample file, point Power Query at the folder, and you are done. Every period when a new file lands in the folder, one click of Refresh pulls it in, applies all the same cleaning steps, and updates your entire report automatically.

Query Parameters — Making Queries Dynamic

A parameter is a named, reusable value that you can reference anywhere in your queries — in filter conditions, file paths, thresholds, or date ranges. Instead of hardcoding a value like "North" or "2025" directly into a filter step, you store it in a parameter and reference the parameter name. To change the value, you update the parameter in one place and all queries using it update together on the next refresh.

Once a parameter exists, use it in filter steps:

In a filter step on the Region column:
  Right-click Region → Filter Rows → Equals → switch dropdown from "Value" to "Parameter"
  → select SelectedRegion

The M code Power Query generates:
  = Table.SelectRows(Source, each [Region] = SelectedRegion)

To change the active region:
  Home → Manage Parameters → change Current Value from "North" to "East" → OK → Refresh
  All queries using SelectedRegion now filter to East automatically.

Parameters in file paths — for switching between environments:
  Parameter name:  FolderPath
  Current value:   C:\Reports\2025\
  Used in:         Source step of every file-based query

  Change FolderPath to C:\Reports\2026\ and all queries point to the new folder instantly.

Parameters are what separate a one-off query from a genuinely reusable reporting tool. A report that filters to a hardcoded region requires someone to open Power Query and edit the M code every time the region changes. A report that uses a parameter only requires a dropdown change in the Manage Parameters dialog — something any user can do without knowing anything about M code.

Query Dependencies — Understanding the Query Chain

In a well-structured Power Query workbook, queries are rarely independent. A staging query imports and cleans raw data. A transformation query applies business logic. A final output query merges and shapes the result for the sheet. Power Query shows you these dependencies visually through the Query Dependencies view.

Setting a query to Connection Only (staging — does not load to sheet):
  In Power Query Editor → right-click query name in Queries pane
  → "Enable Load" → untick
  Or: in Excel → Queries and Connections pane → right-click → Properties → untick "Load to worksheet"

Why use Connection Only queries:
  - Keeps the workbook clean (no extra sheets of intermediate data)
  - Staging queries still run and are available for other queries to reference
  - Only final output queries load to sheets or data models

Viewing the dependency map:
  Power Query Editor → View → Query Dependencies
  Shows a visual diagram of which queries feed into which

Structuring queries this way — raw source → staging clean → final output — is what professional Power Query builds look like. It keeps each query focused on one job, makes debugging easy (click a staging query to see exactly what it produces), and means that if the source file format changes you only update the staging query without touching the output logic.

Section IV Summary — Power Query in Four Lessons

You have now completed the full Power Query section. Here is a quick map of what each lesson covered and the key capability each one unlocked.

Next up — Power Pivot Basics, where we move into Excel's built-in data modelling engine — the tool that lets you connect multiple tables without VLOOKUP, build relationships, and create calculations that work across your entire data model at once.