Forum Discussion

Akshit_Bansal's avatar
Akshit_Bansal
Copper Contributor
Jun 10, 2024

Load only filtered rows in Power Query

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?

  • 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.

  • 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.

  • SGGusau's avatar
    SGGusau
    Copper Contributor
    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.

Resources