How to Load Only Visible or Filtered Excel Rows in Power Query
Question details
The user needs to load only the visible, unfiltered rows from an Excel table into Power Query without including the hidden data.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing an Excel table containing filtered or manually hidden rows into Power Query for further data transformation.
- Observed behavior
- Power Query ignores Excel's row visibility settings and imports the entire dataset, including rows that are hidden or filtered out in the spreadsheet view.
Ensure your source data is formatted as an official Excel Table (using Ctrl+T) so that any added helper columns automatically propagate to new rows.
Use an AGGREGATE Helper Column to Identify Visible Rows
Add a formula-driven helper column to your source Excel table that dynamically flags whether a row is currently visible or hidden before loading it into Power Query.
Power Query reads raw data and does not natively recognize Excel's UI-level row visibility. By utilizing the AGGREGATE function in your Excel sheet, you can create a dynamic visibility flag that updates instantly when rows are filtered or hidden.
In your source Excel table, insert a new column at the far right and name it 'IsVisible'.
In the first data cell of the new column, enter the formula: =AGGREGATE(3,5,[@AnyField]). Replace 'AnyField' with the name of a column that is guaranteed to never have blank cells. The argument '3' specifies the COUNTA function, and '5' tells the formula to ignore hidden rows.
Select any cell inside your table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, locate the 'IsVisible' column. Click the filter dropdown arrow in the column header, uncheck '0', and click OK to keep only the rows with a value of '1'.
Recreate Advanced Filters Directly in Power Query
Instead of relying on Excel's front-end filters and a helper column, apply the exact filtering logic directly within the Power Query Editor.
Try WPS Office for Seamless Spreadsheet Management
If you frequently encounter complex issues with Excel features like Power Query, consider trying WPS Office. It is a free, lightweight, and highly compatible alternative to Microsoft Office that makes filtering, analyzing, and managing spreadsheet data incredibly simple without a steep learning curve.
- 1. Download WPS Office: Visit the official WPS website and download the free office suite for your operating system.
- 2. Install and Launch: Run the lightweight installer and open WPS Spreadsheets.
- 3. Open Your Excel File: Drag and drop your existing .xlsx file into WPS Spreadsheets to instantly view, filter, and edit your data without any format compatibility issues.
Frequently Asked Questions
Why does Power Query automatically load hidden rows from Excel?
Power Query connects directly to the underlying raw data of your table rather than the Excel user interface. Because of this, it ignores UI-level properties like row height, manual hiding, or active autofilters, importing the entire dataset by default.
Can I use the SUBTOTAL function instead of AGGREGATE?
Yes. You can use the formula =SUBTOTAL(103,[@ColumnName]). The '103' argument functions identically to the AGGREGATE setup by counting non-empty cells while intentionally ignoring hidden rows. Both methods are effective for creating a visibility helper column.
Will adding a helper column slow down my Excel workbook?
Adding an AGGREGATE or SUBTOTAL formula to a standard dataset will not cause noticeable lag. However, if your table contains hundreds of thousands of rows, it may cause slight recalculation delays when applying or clearing filters.
