Combine Excel Sheets into One Filtered Table with Power Query
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.

- 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.
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.
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.
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.
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.
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.
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.
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.
Choose Home > Close & Load to place the combined table in Excel. After editing source tables, choose Data > Refresh All to update it.

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.

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.




