cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cymbolz
Helper III
Helper III

Filtering in Power Query Editor not working for OData source

I have an OData source, and when applying a column filter in PQ Editor the data is not at all filtered (same resulting rows as the step before filtering).  I've verified this not just in the preview, but after applying changes back in Power BI. 

 

If I create a table (Enter Data) or query from Excel this filters as expected, so suspect it's something related to the data source.  But unlike a SQL I cannot see anything in the View Native Query option (it's disabled for every step).  I do recall seeing that filtering might pass back to the data source.  If so, is there any way to force it to be done as per the applied step in PQ?

 

The query is:

let
    Source = OData.Feed("https://REMOVED/", null, [Implementation="2.0"]),
    projects_table = Source{[Name="projects",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(projects_table, each ([IsArchived] = false))
in
    #"Filtered Rows"
1 ACCEPTED SOLUTION
cwebb
Resolver II
Resolver II

This seems strange - it might be worth seeing whether query folding is taking place, and what your OData source actually returns, by using this technique: https://blog.crossjoin.co.uk/2018/05/03/troubleshooting-data-refresh-performance-issues-with-odata-d...

 

If that reveals that the filter is being passed back to the datas source but the data source isn't applying the data, you could try the following:

 

let
    Source = OData.Feed("https://REMOVED/", null, [Implementation="2.0"]),
    projects_table = Source{[Name="projects",Signature="table"]}[Data],
    buffer_table = Table.Buffer(projects_table),
    #"Filtered Rows" = Table.SelectRows(buffer_table, each ([IsArchived] = false))
in
    #"Filtered Rows"

This should load the whole of the projects_table table into memory, stop any query folding and force the filter to take place in the Power Query engine. Hopefully the table isn't too big, otherwise this will be slow.

 

HTH,

 

Chris

View solution in original post

5 REPLIES 5
cwebb
Resolver II
Resolver II

This seems strange - it might be worth seeing whether query folding is taking place, and what your OData source actually returns, by using this technique: https://blog.crossjoin.co.uk/2018/05/03/troubleshooting-data-refresh-performance-issues-with-odata-d...

 

If that reveals that the filter is being passed back to the datas source but the data source isn't applying the data, you could try the following:

 

let
    Source = OData.Feed("https://REMOVED/", null, [Implementation="2.0"]),
    projects_table = Source{[Name="projects",Signature="table"]}[Data],
    buffer_table = Table.Buffer(projects_table),
    #"Filtered Rows" = Table.SelectRows(buffer_table, each ([IsArchived] = false))
in
    #"Filtered Rows"

This should load the whole of the projects_table table into memory, stop any query folding and force the filter to take place in the Power Query engine. Hopefully the table isn't too big, otherwise this will be slow.

 

HTH,

 

Chris

View solution in original post

Excellent article, helpful suggestions and problem solved.  Thank you so much Chris.

 

I tried for a quick win, and using either the Table.Buffer (it's a very small table) or some other function (I added an Index column) has, as your blog article showed, stopped the query folding that seems to be the culprit.  

 

I'd love to probe more into it if I get the time, so value the info you included in the article.

 

Have an awesome day,

Matt

I'm glad to know you have things working - however, it's worrying that the filter was not applied properly in the first place. If you can contact the developers of this OData feed you should report the issue so they can investigate.

 

Chris

Certainly, I will follow this up with the devs as there are other errors I'm experiencing that I'm sure are because of their implementation.

One more thing: it might be worth removing the [Implementation="2.0"] record in your first step to see if that affects the filtering.

 

Source = OData.Feed("https://REMOVED/"),

See https://blog.crossjoin.co.uk/2018/06/27/odata-performance-power-bi/ for more details about this setting.

 

Chris

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors