Forum Discussion
Power Query only load filtered rows
I´ve a spreadsheet with filtered rows. I´d like to load only the already filtered data to PowerQuery. Is there a way to do this?
In addition. Not repeat in Power Query exactly the same set of filters as in Excel table you may add helper column to the Table as
=(AGGREGATE(3,5,[@A])=1)+0there [A] is any column within the table assuming it has no blank values. Similar could be for the range. Formula returns 1 for visible rows and 0 for hided ones.
Within the PowerQuery as first steps filter result on zero for this column and remove it. That shall work for any filter applied in Excel sheet.
Devious. I must study that AGGREGATE function more!
- Back in the day that didn't work because filtering did not necessarily trigger a calculation
