Forum Discussion

Cymbolz's avatar
Cymbolz
Helper III
7 years ago
Solved

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,...
Need Help
  • cwebb's avatar
    cwebb
    7 years ago

    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-data-sources-in-power-bi-and-excel-using-fiddler/

     

    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

cwebb's avatar
cwebb
Advocate V
7 years ago

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-data-sources-in-power-bi-and-excel-using-fiddler/

 

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

  • Cymbolz's avatar
    Cymbolz
    Helper III
    7 years ago

    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

    • cwebb's avatar
      cwebb
      Advocate V
      7 years ago

      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

      • Cymbolz's avatar
        Cymbolz
        Helper III
        7 years ago

        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.