Power Query Problems

Combine Excel Sheets into One Filtered Table with Power Query

Rana GarciaRana Garcia Nov 18, 2026 870 views

Question details

Combine columns D and AB from 20 Excel sheets into one table when column B contains 3m, excluding blank output rows and updating the result as source data changes.

Combine Excel Sheets into One Filtered Table with Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
The user has repeated sheet layouts and wants a single filtered table built from their data.
Observed behavior
The desired table contains values from columns D and AB for matching rows, excludes rows where both output values are blank, and can be refreshed when source data changes.
Before you start

Check that each sheet has a header row and that the required fields occupy columns B, D, and AB. Convert each source range to an Excel table so new rows added to the tables can be included on refresh.

Solution 1Recommended

Append and filter the sheets with Power Query

Use this method when the 20 sheets have the same layout and you want a refreshable combined table.

Power Query matches appended columns by header name, not worksheet position. Standardize the three selected column names in each query before appending them.

1
Create source tables

On each sheet, select the data range including its headers, choose Insert > Table, and confirm that the table has headers. Give each table a distinct name on the Table Design tab.

2
Create a query for each table

Click inside the first table and choose Data > From Table/Range. In Power Query, select the fields that came from worksheet columns B, D, and AB, choose Home > Remove Columns > Remove Other Columns, and rename their headers B, D, and AB. Choose Home > Close & Load > Close & Load To, then select Only Create Connection. Repeat for the remaining tables.

3
Append the queries

Open a query from Data > Queries & Connections, then in Power Query choose Home > Append Queries > Append Queries as New. Select Three or more tables, add all 20 queries, and choose OK.

4
Filter matching rows

Open the filter menu on column B and choose Text Filters > Contains. Enter 3m and apply the filter. Use Equals instead if column B must be exactly 3m.

5
Exclude blank output rows

Choose Add Column > Custom Column and name it KeepRow. Enter the formula `([D] <> null and Text.Trim(Text.From([D])) <> "") or ([AB] <> null and Text.Trim(Text.From([AB])) <> "")`. Filter KeepRow to true, then select columns D and AB and choose Home > Remove Columns > Remove Other Columns.

6
Load and refresh the result

Choose Home > Close & Load to place the combined table in Excel. After editing source tables, choose Data > Refresh All to update it.

Append and filter the sheets with Power Query
Refresh behavior: The query does not necessarily update immediately after every edit. Use Refresh All, or check the query properties for refresh-on-open and periodic refresh options available in your Excel version.
Free Microsoft Office alternative

Work with Excel tables in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel files, making it easy to move everyday spreadsheet work between applications. Microsoft Excel is still needed for the Power Query workflow described here.

Open and edit Microsoft Excel XLSX and XLS filesFilter spreadsheet columns to review matching recordsUse a familiar interface for everyday spreadsheet tasksWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Does the filter match only the exact value 3m?

Text Filters > Contains matches cells containing 3m as part of their text. Choose Text Filters > Equals if the whole cell must match 3m.

Will new rows be included in the combined table?

Rows added within the source Excel tables can be included when you choose Data > Refresh All. Make sure each new row is part of its source table.

Why do I need to rename the columns in each query?

Append Queries aligns columns by header name. Rename the fields from worksheet columns B, D, and AB consistently in each query so their values appear in the same output columns.

What happens if only one of columns D and AB is blank?

The KeepRow formula retains the row when either D or AB has a nonblank value. It excludes the row only when both are null, empty, or contain only spaces.

Does Power Query refresh automatically after I edit a cell?

Not necessarily. Choose Data > Refresh All after changes, or check the query properties for refresh-on-open or periodic refresh settings.