Power Query only load filtered rows

Copper Contributor

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?

17 Replies
I don't think you can, you must repeat the filtering in PQ.

@Mischa2710 

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)+0

there [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.

@Sergei Baklan 

 

A good idea ... I´m going to try it. A problem could be that the filtered table is already a PQ table. So if I refresh the table the help column could disappear. But I´ve to test it.

 

Thanks for helping

Devious. I must study that AGGREGATE function more!

@Mischa2710 

If you add helper column to the table returned by PQ such column won't disappear but it isn't in sync with the table. Not very important in this case since we use any value in current row.

 

But I didn't catch how entire model works. You have some source data, transform it by Power Query and return the table to the sheet. Apply filtering to the resulting Table, query now this table and return result into one more table. Like this?

@Sergei Baklan 

absolutely correct. 

For your better understanding. My colleagues filters to their name and after refreshing the last table they get their new table.

@Jan Karel Pieterse 

=(SUBTOTAL(3,[@A])=1)+0

works the same way 

Back in the day that didn't work because filtering did not necessarily trigger a calculation

@Jan Karel Pieterse 

We spoke only about filtering like here

image.png

Calculations - depends on which ones

@Sergei Baklan 

@Jan Karel Pieterse 

Hey guys,

thanks for your support. Was really very helpful. Today, I had time to try it.

 

The last question I have, I understand the result/idea behind of +0, but I don´t understand how it works.

Can you explain this, please.

 

TX

Michael

ah, 0 is for false and 1 for correct ... isn´t it?
The +0 is to convert the result to numeric rather than Boolean, not really needed for your situation
Love this! I can now get rid of some fiddly VBA code which copies selected table records across to a new table.

@Sergei Baklan Thank you for this - a really neat solution - has helped me enormously solve a problem

 

@gvpiper777 

I'm not an author, it's known for a long while. In any case glad it helped.