Get more from Power Query in Excel with these little-known capabilities

If you use Excel or Power BI to work with data, you’ve likely encountered Power Query - and may already rely on it regularly across desktop and web. It pulls data together, cleans it up, and prepares it for analysis. But beyond those familiar tasks, Power Query includes several capabilities that can make solutions more flexible, scalable, and easier to maintain.

The capabilities below are especially useful when you want Power Query solutions to be easier to maintain, faster to refresh, and more reusable across similar workflows.

Parameters that act like smart controls

Parameters allow you to separate logic from configuration. Instead of editing queries every time something changes, you simply adjust a parameter.

With parameters, you can:

• Switch data sources (for example, Test → Production)
• Control date ranges (Last 7 days vs. Last 12 months)
• Enable or disable filters
• Pattern: use a True/False parameter and write your filter step as an if (apply the filter only when enabled), or branch queries and select the branch based on the parameter.
• Drive logic across multiple related queries

Because parameters are shared across queries, they can be referenced by any other query in the workbook, and become a single source of truth.

For intermediate workflows: parameters are evaluated at refresh time and are best for configuration (environment, date window, thresholds). For per-row/per-file logic, use a function (covered below).

To create a parameter and reference it to drive a filter and a source toggle:

1. To open Power Query in Excel, select your table or data range, and then click Data > From Table/Range (or Get Data and choose your source).
2. To create a parameter, in the Power Query window, select Home > Manage Parameters > New Parameter, and then reference the parameter in a step.
3. To use multiple queries, right‑click your query, duplicate or reference it, and then  use the same parameter to control filters across those queries.
4. To toggle data source with a parameter, create another parameter and branch the Source step to pick connection strings/paths based on Env.
5. Switching sources can change credential and privacy behavior; test refresh end-to-end in each environment, and re-check privacy level settings if you hit unexpected prompts or firewall errors.
6. When you’re ready to publish or use the parameter, select Close & Load and then click Data > Refresh/Refresh All. Adjust Manage Parameters values as needed, and watch downstream queries update.

Query folding: letting the source do the work

Query folding is one of Power Query’s most important performance features - and one of the least understood. In simple terms, it means Power Query can translate some of your transformation steps into operations that run at the data source instead of in the local engine. When that happens, less data needs to be transferred, and refreshes can be significantly faster.

The following diagram demonstrates the steps that take place in this optimization process.

1. The M script, found inside the advanced editor, is submitted to the Power Query engine. Other important information is also supplied, such as credentials and data source privacy levels.
2. The Query folding mechanism submits metadata requests to the data source to determine the capabilities of the data source, table schemas, relationships between different tables at the data source, and more.
3. Based on the metadata received, the query folding mechanism determines what information to extract from the data source and what set of transformations need to happen inside the Power Query engine. It sends the instructions to two other components that take care of retrieving the data from the data source and transforming the incoming data in the Power Query engine if necessary.
4. Once the internal components of Power Query receive the instructions, Power Query sends a request to the data source using a data source query.
5. The data source receives the request from Power Query and transfers the data to the Power Query engine.
6. Once the data is inside Power Query, the transformation engine inside Power Query (also known as mashup engine) does the transformations that couldn't be folded back or offloaded to the data source.
7. The results derived from the previous point are loaded to a destination in Excel.

In simple terms, query folding determines whether Power Query can translate your steps into operations that run directly at the data source (most commonly in databases such as SQL Server), rather than pulling everything locally and processing it row by row.

Why it matters

When query folding is preserved:

• Less data is transferred
• Refresh times are dramatically faster
• Large datasets stay manageable

When folding is broken, Power Query still works - but it does more of the effort itself.

To preserve folding so filters/join logic runs at the source (for example, in SQL Server):

1. Start with a connector that commonly supports folding (for example, Data > Get Data > From SQL Server Database). Many file/web sources (including some SharePoint patterns) won’t fold the same way - so validate folding as you build.
2. In Power Query, apply filters as soon as you have the columns you need (and before expensive steps like merges/expands where possible) (e.g., only current fiscal year).
3. Prefer fold-friendly steps (filters, removing columns, simple merges/joins, grouping). Note: Group By often folds (especially simple aggregations), but “All Rows” typically breaks folding.
4. To check folding, right-click a step and select View Native Query when available. If it’s disabled, folding may be broken or the connector doesn’t expose a native query - use this as a signal to test performance and keep earlier steps as simple as possible.
5. When supported, check it step-by-step: right-click successive steps to find the first step where View Native Query becomes unavailable - that’s often where folding stops.
6. To delay custom columns, add custom columns/calculations later in the chain to avoid breaking folding prematurely.
7. To validate performance, refresh and note timings, and then compare with/without early filters.

Turning queries into reusable functions

If you’ve ever copied the same transformation steps from one query to another, you’ve already done the hardest part of creating a Power Query function.

 

What many users should know

Any query can be turned into a function - often by adding a single parameter. Once created, that function becomes a reusable building block you can apply anywhere.

Why functions are a gamechangers

Reusable functions help you:

• Standardize cleanup logic
• Reduce copy‑paste errors
• Apply consistent transformations across dozens - or hundreds - of datasets

Instead of rewriting steps, you apply the function like a tool.

To convert an existing query into a function and apply it broadly (intermediate-friendly pattern):

1. Start with a working query that transforms one input (for example, one file).
2. Identify what should vary (for example, FilePath as text). Instead of a global “Manage Parameters” value, you’ll make it a function argument so it can change per file/per row.
3. Open Advanced Editor and replace the hard-coded source (path/URL) with the argument (for example, use File.Contents(FilePath) or the appropriate connector call that accepts a path).
4. Wrap the query in a function signature and name it (example): 

   FileTransform = (FilePath as text) as table =>
   let
     Source = Excel.Workbook(File.Contents(FilePath), true),
     Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
     PromotedHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true]),
     ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {{"Date", type date}, {"Amount", type number}})
   in
     ChangedTypes

   NOTE: The Sheet1 reference is a “happy path” example. In real-world folder ingestion, prefer importing a named Table (more stable than sheet names) or add defensive logic when sheet/table names can vary.

5. To apply the function to many files, create a table of file paths (or use Data > Get Data > From Folder to get a list of binaries), then use Add Column > Invoke Custom Function to call your function for each row. NOTE: If you hit Formula.Firewall or unexpected privacy prompts, review privacy level and try to avoid combining unrelated sources in a single query (or separate staging queries to isolate sources).
6. To combine results: use Expand (when you invoked a function returning a table) or use Append Queries to stack tables with the same schema. Use Combine Files specifically when starting from From Folder and you want Power Query to generate the sample-file pattern for you. NOTE: If you hit Formula.Firewall or unexpected privacy prompts, review privacy level and try to avoid combining unrelated sources in a single query (or separate staging queries to isolate sources). TIP: When ingesting many files, add basic resilience so one bad file doesn’t break refresh (for example, use try … otherwise around the function body or filter out error rows after invoking the function).

Folder + sample file pattern (automated ingestion)

Many people know Power Query can import data from a folder - but fewer explore how sophisticated the sample file pattern really is.

When you connect to a folder, Power Query automatically:

• Selects a representative sample file
• Applies transformations to that file
• Reuses the logic for every file in the folder

Why this matters

With the right setup, Power Query can:

• Handle hundreds or thousands of files
• Automatically include new files as they’re added
• Enforce a consistent schema over time

The result feels less like a spreadsheet and more like a lightweight data pipeline.

How to use a folder connector with a robust sample file transform

1. To connect to a folder, select Data > Get Data > From Folder and select a folder.
2. In the Power Query window, review the file metadata (Name, Extension, Date modified), and then click Transform Data.
3. Power Query creates helper queries (for example, Sample File, Transform Sample File, and the main combined query). Edit Transform Sample File - Power Query applies that logic to every file in the folder.
4. In Transform Sample File, perform all cleanup (remove top rows, promote headers, set types). TIP: Keep most transformations in Transform Sample File. Avoid heavy edits in the auto-generated helper queries unless you’re intentionally changing the pattern.
5. In the main query, keep Name and Date modified to preserve lineage.
6. To validate the connection, drop a new file into the folder and notice that the added data is automatically included.

TIP: Avoid adding complex logic directly in the auto‑generated helper queries unless you intentionally want to change the pattern—doing so can make future edits harder to reason about.

Applied Steps pane: your logic map

The Applied Steps pane may look like a simple audit trail, but it’s actually the heart of Power Query’s logic.

What’s often overlooked

Step order affects:

• Performance
• Query folding
• Long‑term maintainability

Renaming and reordering steps isn’t cosmetic - it’s foundational.

Best practices experienced users follow

• Rename important steps so intent is clear
• Group transformations logically (Filter → Shape → Enrich)
• Be intentional with Changed Type steps - auto-detected types can make refresh brittle when schemas drift (and type steps can affect folding depending on connector).
• Review steps periodically as workflows evolve
• In Excel, keep intermediate/staging queries as connection-only (disable load) and load only the final outputs you actually need.

Treating Applied Steps like readable code pays off over time.

 

To make steps readable, improve performance, and avoid accidental logic breaks:

1. In Power Query, locate the Applied Steps list.
2. To rename critical steps, right‑click a step name, and then click Rename (e.g., Filter_FY25, Join_ProductDim).
3. To group transformations, select Filter > Shape > Enrich (filters first, column cleanup, then joins/calculations).
4. To safely re‑order steps, drag them upward or downward carefully, and check dependencies (e.g., a step refers to a column removed earlier) if an error appears.
5. Periodically review steps as workflows evolve, and delete obsolete steps only after confirming no later steps depend on them (then rename steps so they read like a story).
6. After re-ordering, check folding where supported (right-click a step and select View Native Query). If it’s not available, validate via refresh time and by confirming you aren’t pulling unnecessary rows/columns early.

Feedback

We want to hear from you about how these features and others can make working with Power Query more effective. If you have any requests or suggestions, select Send Feedback or use the respective feedback icons.

 

Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!