Power BI and Dynamics CRM Online 2016 - Querying Odata RRS feed

  • Question

  • Hi all, we are using CRM as an Incident tracker only (no sales) and doing all of our reporting on Power BI using the Odata feed URL of our CRM instance. I have built the reporting in Power BI Desktop. I import both IncidentSet and EmailSet tables and using the EmailSet table to perform some SLA calculations. 

    In our CRM workflow, emails come in are manually assigned/associated to a queue and this value is stored in a custom field in our case form. The problem is that the EmailSet table has gotten enormous (multiple gigs) and it has to load ALL data before this filtering can occur. This churn is causing my scheduled refresh to fail in the Power BI Service. 

    What I would like to do is to somehow filter the EmailSet table *before* loading into Power Query. The specific issue is that, in order to filter my results, I have to expand two columns deep (expand one column, and then expand one of the resultant expanded columns) within Power Query, and then filter on some specific values on one of the expanded columns. My question is, is there a way to essentially "pre-filter" the table before it is loaded into Power Query. I am aware that you can pass commands/parameters inline in the OData feed string (such as is described here I am wondering if this is possible for what I want to accomplish, and if Power BI Desktop supports this. 

    Thanks in advance for any help, Karen 

    Thursday, March 2, 2017 10:29 PM