cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexEr_
New Member

Basic filter in power query editor is not filtering

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!

 

Snag_55c155b.png

2 ACCEPTED SOLUTIONS

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.

View solution in original post

HotChilli
Super User
Super User

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

View solution in original post

7 REPLIES 7
HotChilli
Super User
Super User

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

View solution in original post

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

 

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
Super User
Super User

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.

 

PBI_SuperUser_Rank@1x.png  

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

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.

View solution in original post

HotChilli
Super User
Super User

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)

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. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.