Basic filter in power query editor is not filtering

[AlexEr_]

Hello!

I have a problem with the power query editor when I try to filter any column in any way. The rows will remain both in and outside the editor. Even a true/false column will not filter correctly to show either true or false depending on which I choose. Refreshing preview is not helping. I'm worried I have changed some setting I'm not aware of. The problem persists between pbix-files and over time. Any input appreciated!

[AlexEr_ · Accepted solution]

Hi Anand and thank for your reply!

For the true/false-column I use the drop-down to deselect false. Case sensitivity should not be an issue for a boolean I'm guessing?

I have successfully filtered now by creating an index column before the filtering, as this somehow has something to do with something called query folding which I know nothing about (see post in other comment thread for link), but the index column seem to work.

[HotChilli · Accepted solution]

Well done on finding a way round it.

I found some posts and this in the ideas forum https://community.powerbi.com/t5/Issues/Bug-Filtering-in-Query-Editor-not-working/idi-p/442857 

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).

[anece]

@HotChilli 

do you know how to set the OData properties, which @jamesf has used in his original post?

---

@HotChilli wrote:

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).

[AlexEr_]

I would never have guessed that it's a specific problem with odata but your questions and suggestion highlighted the proper end in which to begin investigation. Thanks a lot for the help and explanation! 

[Anand24]

Hi @AlexEr_ ,

How are you trying to filter them? Directly using M Code or using dropdown filtering option(preferred).

Also, please look at the case sensitivity. M code used in DirectQuery mode is case-sensitive. Please re-check it once.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[AlexEr_ · Accepted solution]

Hi Anand and thank for your reply!

For the true/false-column I use the drop-down to deselect false. Case sensitivity should not be an issue for a boolean I'm guessing?

I have successfully filtered now by creating an index column before the filtering, as this somehow has something to do with something called query folding which I know nothing about (see post in other comment thread for link), but the index column seem to work.

[HotChilli]

What's the data source? Is it always the same ?

Have you tried a very simple table using 'Enter Data' and then filtering it to see if that works?  (to start troubleshooting)

[AlexEr_]

Hi! Thanks for your reply!

The data source is an odata feed where the data gets imported.

I made a table with one column with either true or false and populated it with 1000+ rows. It was possible to filter correctly interestingly enough.

I found this thread after searching for filtering on odata: https://community.powerbi.com/t5/Desktop/Importing-Data-from-OData-URL-but-applying-a-filter-on-dyna...

I'm not sure I understand it correctly, does it mean that filtering an odata source can't be done the normal way?

Edit: I tried adding an index column before the filter step and suddenly the filter works.