cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Willborn Member
Member

Filter does not work

Hi there

 

I wanted to set a filter on a column in PBI Deskto (editing queries). The text filter is set on the content of the field (subject text contains "client").

 

= Table.SelectRows(#"Inserted Days", each Text.Contains([Subject], "client"))

 

 

There has been 1 prepared record for tests which contained this text "client" and 12 others, out of thousands. The prepared record is shown in the preview correctly, togheter with others. Totally 13 rows are displayed.

 

So far no problem, but:

 

As soon as I click on "Close & Apply", PBI Desktop only shows 12 rows, and excactly my testing record dissapeared.

Does someone know what happened here?

 

Thanks for any hint!

Patrick

2 ACCEPTED SOLUTIONS

Accepted Solutions
austinsense Established Member
Established Member

Re: Filter does not work

This is tough without being able to see the query but my thoughts are ...

1) are there other steps in the query that could be hiding the row?

2) is the word "client" all in lowercase - PQ is case sensitive

3) how did your test row make it into the dataset? is it from the same data source as the other data?

4) finally, i've noticed that sometimes the query preview feature doesn't work exactly right - that could be what you're dealing with

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

Willborn Member
Member

Re: Filter does not work

 

Here the screenshots for easier understanding of how preview is ignoring case sensitive text filter and for those might having the same issue.

Filter by lower case "client reponse" will find the test data record with text "Client Reponse" in Query Editor preview...
PQ_Filter_1.PNG

 

...but not as a result:

PBI_Filter_2.PNG

Filtering by upper case "Client Reponse" will find the test data record in Query Editor preview.

PQ_Filter_3.PNG

...and also the data record, as the filter is case sensitive. But this is ignored by the preview...

PBI_Filter_3.PNG

 

Best regards

Patrick

View solution in original post

7 REPLIES 7
austinsense Established Member
Established Member

Re: Filter does not work

This is tough without being able to see the query but my thoughts are ...

1) are there other steps in the query that could be hiding the row?

2) is the word "client" all in lowercase - PQ is case sensitive

3) how did your test row make it into the dataset? is it from the same data source as the other data?

4) finally, i've noticed that sometimes the query preview feature doesn't work exactly right - that could be what you're dealing with

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

Willborn Member
Member

Re: Filter does not work

Hi Austin

 

Thank you! Your 2nd point just guided me to the answer - which seems to be a bug in the preview. The filter in Query Editor is not case sensitive, but PQ is... I'll send a fraun to MS.

 

Btw: The data is loaded from our on-premise CRM Test system, the data source is the same for all records.

 

Willborn Member
Member

Re: Filter does not work

 

Here the screenshots for easier understanding of how preview is ignoring case sensitive text filter and for those might having the same issue.

Filter by lower case "client reponse" will find the test data record with text "Client Reponse" in Query Editor preview...
PQ_Filter_1.PNG

 

...but not as a result:

PBI_Filter_2.PNG

Filtering by upper case "Client Reponse" will find the test data record in Query Editor preview.

PQ_Filter_3.PNG

...and also the data record, as the filter is case sensitive. But this is ignored by the preview...

PBI_Filter_3.PNG

 

Best regards

Patrick

View solution in original post

Microsoft pqian
Microsoft

Re: Filter does not work

@Willborn Can you elaborate a little bit on "Query Editor is not case sensitive"? 

 

The queries editor *IS* the power query editor window, and it should reflect the true output from the underlying query engine. Can you share the full query?

Microsoft pqian
Microsoft

Re: Filter does not work

@Willborn Just saw your update, this is weird.

 

Can you post the full query here?

Willborn Member
Member

Re: Filter does not work

Will do some further testing with other data sources - but most probably with the same result... keep you informed.

Microsoft pqian
Microsoft

Re: Filter does not work

@Willborn I tested with a sample OData source:

let
    Source = OData.Feed("http://services.odata.org/northwind/northwind.svc/"),
    Customers_table = Source{[Name="Customers",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Customers_table, each Text.Contains([ContactTitle], "owner"))
in
    #"Filtered Rows"

Here all "Owner" rows are returned in both the Preview and the final loaded data model, so OData text filtering is case insensitive (it's source dependent, since the substringof operator in OData is case insensitive.) FYI, the above query is executed directly on the service with the URL

http://services.odata.org/northwind/northwind.svc/Customers?$filter=substringof(%27owner%27,ContactT...

 

I find that Preview and Data Model yielding different results extremely troublesome...I hope to get to the bottom of this, as I suspect it may not be the case sensitivity issue you mentioned.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors