Hi Matthias,

From memory there are some bugs with filters. However, if your query updates then the filter would need to be reapplied to the Excel Table, because filters are set and forget, not dynamic.

Have you tried adding the SUBTOTAL formula in Power Query? This is the most efficient way to add calculated columns.

Mynda

Hi Mynda,

without that formula column there are no filter issues. Actually also the table length did not change as the data itself is unchanged.

I brought in a new custom column with each "=SUBTOTAL(3;ProjectStatus[@[SFI]:[YE]])". The idea was to use this to filter out unvisible rows (Subtotal is the only formula I know which reacts to filtering) in some sumifs formulas (not part of the table). And I wanted to have it in the loaded table itself in order not to have to worry about the ranges at all.

I did not add a column next to the loaded query (that would expand the table), but through the query editor. But indeed in a way, that it somehow emulates writing it by hand.

It works, just the filtering starts to get weird after the formulas are turned to numbers. So is there any way to cure the filtering behaviour or is there an alternative formula/way to indicate invisible rows?

Regards,
Matthias

Hi Matthias,

Can you please upload a sample Excel file so I can better understand what you're working with and trying to achieve.

Mynda

Hi Mynda,

I simplified the query and I hope it illustrates what I explained with words in my above two posts.

Thanks for the endeavors,
Matthias

Hi Matthias,

The alternative solution depends on those sumifs you are talking about. But most probably will be a simple pivot table that can count and add rows based on slicers selections. Everything you already have on the sample file can be produced by a pivot table, no need to create them with formulas.

The SUBTOTAL can be replaced with:

= Table.AddColumn(Source, "Count", each List.NonNullCount({[Q2], [Q3], [Q4]}), Int64.Type)

Hi Catalin,

Bad that we would have to go for an alternative solution.

Nice idea, and the sumifs themselfs are not the problem: I could emulate the list by a pivot and add a simple status summary pivot next to it (to replace the sumifs) and steer them both by the same slicers.

In the actual file I have already a separat pivot, which groups nicely. You can add there more fields and thus also more filter possibilities (product group, won/pending, ...).

But...

The "loaded query" list was intended to be very simple and to focus on the status and status changes. The list is in reallity a self referencing table of the status of all projects, where whoever is discussing can add what was discussed, follow ups, due dates, etc. in comment columns (on the right of the list). This I can not emulate by a pivot.

As you normally discuss only subsets of the projects, I wanted to have a sumifs summary on the top showing the status changes according to the filtering made for regions or customers.

= Table.AddColumn(Source, "Count", each List.NonNullCount({[Q2], [Q3], [Q4]}), Int64.Type) does count how many columns of Q2-Q4 do have a value (all rows are >0). But they keep this value independent of the filtering which is applied. The subtotals instead turn to 0 when they are filtered away (invisible). Their only purpose is to be used as criteria of the sumifs.

Hi Matthias,

Do you have the same filtering issues on the sample file you sent? I as not able to reproduce the issues you described, no matter how many times I refreshed the query, tried to filter all columns.

 I would check a few things:

-make sure PQ is updated to the last version

-do you have any other formulas referring to the current sheet in the table you are filtering?

-copy the table produced by PQ to another sheet, remove any attached query, convert it to a range, convert back to a table, try again the filters.

-try a UDF function instead of SUBTOTAL:

In the filter column, add this formula: =CountNonHidden(Data_2[@[Q2]:[Q4]])

Hi Catalin,

thanks for the follow up!

Yes I have the issues. And you can see that the lines 104, 106, 108 and 110 are "missing" although no filter is applied. This line hiding is the result of the filtering problems.

If you do not have any issues, I hoped it can be cured by a new PQ version. But the problem seems to be independent of PQ*.

- no formulas in the sheet beside of the summary on the top, and in the original file index match from another sheet is picking up the info from 2 comment columns (which are right of the Q4 column)

- I converted to range and it brought back immediately the hidden lines. After converting to table the filtering issues started anew (*means not PQ problem). Without converting to table, the filtering is a bit different as only the last row is not filtered correctly and this unfiltered range is not growing as it is in case of the tables.

- UDF was the first thing I tried, but as I am no good at macros, I probably made something wrong. I will try yours. But as it is basically the same way of bringing it to [filter] it might render similar results.

PQ version: 2.49.4831.381 32-bit, so indeed not the freshest one

attached the sample file once more, this time with:
-filtered query to demonstrate the issue
-one sheet as range (not converted back to table), showing here the last row is not filtered correctly
-one query with integrated UDF formula, don't know why it doesn't work

Check your excel updates also, maybe you hit an old solved bug, who knows.

I noticed that your loaded query s not looking right, but when I apply the same filter, works on my side.

UDF: the module name must not be the same as procedure name.

Nothing else comes to my mind, there is no logical reason for this behavior, there are no blank rows that can interfere with the filter range.

Hi Catalin,

new PQ Version: 2.54.4970.321 (PQ-CY18SU01) (x86) i.e. 32-bit

Unexpected Error
Could not load file or assembly 'Localytics, Version=13.0.0.0,
Culture=neutral, PublicKeyToken...
or one of its dependencies

Trying to update the querey nonetheless gives this message:
Initialization of the data source failed. Check the data base server ...
Which is strange as the data source is CurrentWorkbook.

So I am stuck for a while on the query side.

UDF: the module name must not be the same as procedure name. Ahhh! Thanks!

It worked after changing the module name and it survived also several filterings. Replacing it manually with the subtotal formula brought immediately about the filtering issues.

A disadvantage of using UDF could be that you have to have the content activated and by default it is deactivated. Correct? So I still hope that it will work with subtotals.

Update:
I deinstalled and reinstalled PQ. And it works now. But it does not cure the subtotal filtering problem. The issues applies after turning the formula to figures. So it is not a query issue but how subtotals affects the autofilter in a table.

Matthias said

A disadvantage of using UDF could be that you have to have the content activated and by default it is deactivated. Correct? So I still hope that it will work with subtotals.

Not sure what you mean by having the content activated, should work in any context.

Hi Catalin,

e.g. "SECURITY WARNING: Some active content has been deactivated." You have to press "Enable Content" before any macro could run.

Well, there is a cure for that.

At Workbook_Open, a code can unhide the necessary sheets, only if macros are enabled. If not, users will see just a "welcome" page.