Power Query Problems

How to Name and Join Filtered Tables in Power Query

Natalie Taylor Sep 30, 2026 869 views

Question details

The user needs to create two distinct filtered tables from a single source dataset and join them together within a Power Query script.

How to Name and Join Filtered Tables in Power Query

Product: Power Query / Spreadsheet
Device & OS: not provided
Scenario: Writing a custom Power Query script (M code) to process and merge multiple filtered views of the same dataset.
Observed behavior: Renaming queries in the Queries pane does not make those names automatically available as variables in a combined script, leading to reference errors when attempting to execute the join step.

Before you start

Ensure your source data is successfully loaded into Power Query and that you are familiar with accessing the Advanced Editor to modify the M code 'let' expression.

Solution 1Recommended

Define Reusable Intermediate Steps in a Single Let Expression

Instead of relying on separate queries in the Queries pane, define and name your filtered tables as intermediate steps within the same query so you can reference them directly in your join function.

In Power Query M code, the 'let' expression evaluates a series of steps sequentially. By defining your filtered tables as named steps within the same block, you create local variables that can be passed seamlessly into join functions like Table.NestedJoin.

Open the Advanced Editor

From the Power Query Editor, go to the 'Home' tab and click on 'Advanced Editor' to view the underlying M code for your query.

Load and Type the Source Data

Ensure your source data is loaded only once at the beginning of the 'let' block. For example: `Source = Excel.CurrentWorkbook(){[Name="MyData"]}[Content]`.

Create the First Filtered Table Step

Define a new step to filter the source for your first condition. Name it clearly without spaces, such as: `TableMean = Table.SelectRows(Source, each [Type] = "Mean")`.

Create the Second Filtered Table Step

Define another step for your second condition using the original source step: `TableSTDV = Table.SelectRows(Source, each [Type] = "STDV")`.

Join the Intermediate Tables

Use the Table.NestedJoin function to merge the two steps you just created. For example: `MergedTables = Table.NestedJoin(TableMean, {"ID"}, TableSTDV, {"ID"}, "CombinedData", JoinKind.Inner)`.

Expand and Return the Final Result

Expand the joined column as needed using `Table.ExpandTableColumn`, perform any final cleanup, and ensure the final step name matches the one specified in the 'in' clause at the bottom of the script.

Define Reusable Intermediate Steps in a Single Let Expression

Best Practice for Step Naming: Using step names without spaces (like TableMean instead of #"Table Mean") makes your code cleaner and much easier to reference as variables later in the script.

Free Microsoft Office alternative

Need a Lightweight Alternative for Data Processing?

While complex Power Query M scripting is specific to Microsoft Excel, WPS Office provides a highly compatible, free, and lightweight alternative for everyday spreadsheet tasks, data analysis, and advanced filtering.

1. Download and Install WPS Office: Visit the official WPS Office website to download the free installer for your operating system.
2. Open Your Spreadsheet Files: Launch WPS Spreadsheet and open your existing Excel workbooks seamlessly to continue working.
3. Utilize Built-in Data Tools: Use the 'Data' tab to apply advanced filters, create PivotTables, and consolidate data effortlessly.

Completely free to use with a lightweight installation packageHighly compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Familiar user interface for seamless migration without a learning curveRobust built-in data analysis tools, advanced filters, and PivotTables

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an error when trying to reference a query name from the Queries pane in my script?

Queries in the Queries pane act as separate scripts. Unless they are correctly evaluated and loaded as functions or global variables, one script cannot natively 'see' a local variable from another without proper referencing syntax. Defining variables inside the same 'let' expression avoids this issue.

How do I reference a step name that contains spaces in Power Query?

If a step name has spaces, Power Query requires you to wrap the name in double quotes and prefix it with a hash symbol, such as #"Filtered Table". It is highly recommended to use camelCase (e.g., FilteredTable) to simplify variable referencing.

Can I join a table to itself in Power Query?

Yes, you can perform a self-join. You simply use the same previous step name for both the left and right table arguments in the Table.NestedJoin function.

Does WPS Spreadsheet support Power Query scripts (M code)?

WPS Spreadsheet currently offers powerful data analysis and extraction tools natively, but advanced Power Query M code scripting is a proprietary feature of Microsoft Office. You can process complex datasets in WPS using PivotTables, Advanced Filter, and built-in functions.

Excel & Data Problems