Problem with power query and user added data filtering

[Pulleritz]

Hello,

I have one data sheet (tst1.xls) and by power query, i transfer data to another excel document (tst2.xls) where i add manually one column.

Problem occurs filtering the data in tst2.xls. For example, first i filter power query data (column data - value 1) and after that column data3 - value x. Then all information is messed up.
Is there any trick to avoid the mess?

Thanks,
Siim

[sandy666]

These are two independent filters.
Query table has own filtering and range data3 has own.

You can try to select whole range data1, data2 and data3 and choose filter from Data - Filter - you'll see you can't do that
The same you cannot select as above and Insert - Table

[Pulleritz]

I can see but any clever hint still to filter both? Or is it absolutely impossible?

[sandy666]

sure, in short
remove query table and data3 range from the sheet, then...
New blank query
create list from data3 values
change to table
---
add index for both
merge queries by index
remove unnecessary columns
load merged table

[sandy666]

or easier

create pivot table from whole range data, data1 data3
tabular form
repeat all item labels
subtotals off
totals off

hard to say more because you didn't say what you want to achieve (filtering is not a goal in this case)