SOLVED

Load only filtered rows in Power Query

Copper Contributor

I have a source excel table on which I use Power Query to get a desired excel table (say Table1).

 

Now further I want to run a Power Query on Table1 based on the filtered rows only (which I want the query to pick up dynamically) to get another table.

 

How do I make Power Query pick up only the filtered rows from Table1?

2 Replies
best response confirmed by Akshit_Bansal (Copper Contributor)
Solution

@Akshit_Bansal 

Power Query itself doesn't recognise is row filtered or not. You may add helper column to the source table, e.g. named as IsHidden with formula like

=AGGREGATE(3,5,[@AnyField])

IsHidden returns zero for hidden rows and 1 otherwise. Power Query could be filtered based on that field.

Duplicate Table1. Use the filter to filter out or keep only desired rows. To keep it dynamic, consider using the advanced filtering based on conditions you determine.
1 best response

Accepted Solutions
best response confirmed by Akshit_Bansal (Copper Contributor)
Solution

@Akshit_Bansal 

Power Query itself doesn't recognise is row filtered or not. You may add helper column to the source table, e.g. named as IsHidden with formula like

=AGGREGATE(3,5,[@AnyField])

IsHidden returns zero for hidden rows and 1 otherwise. Power Query could be filtered based on that field.

View solution in original post