topic Re: Filtering not working in Power Query

Filtering not working

anece — Wed, 24 Nov 2021 12:34:03 GMT

Hi,

I am trying to filter rows by data in a certain column. The original (odata) dataset has about 50k rows.

This is how the data looks like originally:

When I try to filter

= Table.SelectRows(Timeentries_table, each ([FK_RessourceID] = 1))

the results do not show the proper rows. I still get rows displayed which have a different value than 1 in column FK_RessourceID.

As soon as I reduce the rows by

= Table.AlternateRows(#"Gefilterte Zeilen",0,1000,1)

and reapply the filter

= Table.SelectRows(#"Entfernte alternative Zeilen", each ([FK_RessourceID] = 1))

then the right rows are being displayed (those which have a [FK_RessourceID] = 1).

Any idea what I am doing wrong?

Re: Filtering not working

Anonymous — Wed, 24 Nov 2021 14:48:29 GMT

Sometimes there are issues when referring to the navigation step. Try adding a new blank step after the navigation step, and then refer to that new step in your SelectRows filter.

--Nate

Re: Filtering not working

anece — Wed, 24 Nov 2021 15:36:00 GMT

Thanks for your suggestion. Doesn't work.

If you read closely - filtering is my very first step. That first step fails already.

Re: Filtering not working

Anonymous — Wed, 24 Nov 2021 16:20:04 GMT

Steps should look like:

Source

Navigation

NewEmptyStepName

Table.SelectRows(NewEmptyStepName,...

#"Your Next Filter"

That didn't work?

--Nate

Re: Filtering not working

anece — Wed, 24 Nov 2021 16:23:44 GMT

Thanks for the suggestion again.

I have tried it - it doesn't work.

Hopefully it was clear that reducing the number of rows seems to make the filter work. I don't know why.

Re: Filtering not working

HotChilli — Wed, 24 Nov 2021 16:59:29 GMT

Apologies for intruding but isn't this the "you have to stop the query from folding to make it work issue"?

If the query folds, it gets sent back to OData source and, depending on the settings there, the filtering doesn't work

Re: Filtering not working

anece — Wed, 24 Nov 2021 20:28:02 GMT

Hey HotChilli,

Thanks for your reply. Can you kindly elaborate on what you mean when the query starts to fold? Which settings do I need to adjust to get the expected results?

Re: Filtering not working

HotChilli — Wed, 24 Nov 2021 21:06:00 GMT

search for powerbi query folding and see these threads:

https://community.powerbi.com/t5/Desktop/Basic-filter-in-power-query-editor-is-not-filtering/td-p/2147457

https://community.powerbi.com/t5/Power-Query/OData-filter-pass-through/td-p/155386

Re: Filtering not working

smpa01 — Wed, 24 Nov 2021 21:06:52 GMT

anece is it possible to filter on the server side before you bring the data in PQ,; i.e. filtering in OData Rest URL itself?

https://localhost:7048/DynamicsNAV/OData/Company/Customer?$filter=FK_ResourceID eq 1

Re: Filtering not working

anece — Wed, 24 Nov 2021 22:21:51 GMT

smpa01, thanks for the suggestion and possible workaround. Unfortunately this is not possible. I don't have any control over the data which is being presented to me via odata. 😞

Re: Filtering not working

anece — Thu, 25 Nov 2021 10:31:51 GMT

HotChilli - thank you.

Along with the posts you linked to, and your troubleshooting, it's an odata source problem.

Query Folding is when the Power Query transforms get rolled up and sent to the data source for processing. We always aim for it because it makes for speedy and efficient processing. Unfortunately with OData, it seems the transforms won't work unless there are certain properties set in OData (that's from the investigations jamesf did in one of those other posts).

So to get round this, we have to break Query Folding (you've done this with an Index, I think). The post I linked to uses Table.Buffer to load the table in memory and that worked too.

Hope this helps (and for anyone else looking for a solution).

That seems to be the issue indeed. The source doesn't seem to handle the filtering requests accordingly. Buffering the entire table seems to do the trick.

Just to be 100% sure: Do you know how I can see which requests are being sent to the server? Since it is https I can't simply sniff with Wireshark (and I don't want to fiddle around with a proxy, tunneling etc). Is there a kind of log in Power Query where I can see the communication between the client and the server?

Re: Filtering not working

Anonymous — Mon, 29 Nov 2021 08:08:43 GMT

Hi anece ,

Do you want to know the knowledge about query diagnosis?

Go to File->Option and settings->Options->Diagnostics, you will find the logs if tracing is enabled by clicking "Open traces folder".

Here's a good tool to analysis the logs. Power BI Desktop Trace Logs Analyser

You may refer to: Query Diagnostics | Microsoft Docs

Recording Query Diagnostics in Power BI | Microsoft Docs

Solved: Where i found the error log of date refresh on Pow... - Microsoft Power BI Community

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.