Filtering not working

[anece]

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?

[smpa01]

@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

[anece]

@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. 😞

[HotChilli]

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/21... 

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

[anece]

@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?

[Anonymous]

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.

[HotChilli]

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

[anece]

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? 

[Anonymous]

Steps should look like:

Source

Navigation

NewEmptyStepName

Table.SelectRows(NewEmptyStepName,...

#"Your Next Filter"

That didn't work?

--Nate

[anece]

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.

[Anonymous]

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

[anece]

Thanks for your suggestion. Doesn't work.

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