When you connect Power BI to Dynamics 365, it pulls all the records from the entities that you have selected. That is generally a good thing, but there are situations where that is very inconvenient.
Maybe you are dealing with large datasets, or you need to filter what records are available for security purposes.
If you have just a few 100k records, Power BI can take over 40 minutes to pull all the records. That is frustrating if you are just going to filter them anyway and only use a small subset of the data.
Or if you are creating a Power BI dashboard intended for sharing, and you want to make sure the dataset does not have records the users are not supposed to have.
You can get around that by amending the OData URL you use in Power BI. Credit goes to my colleague and Dynamics 365 Wonder Woman Michelle Eldridge who told me about this trick recently.
Prefilter records in OData
Here is an example of an OData connection URL for Accounts with parameters added.
The $select specifies the fields it will include. The $filter defines the filter that is applied. In this case, only records in the state of California.
Use the URL in Power BI
Click on Get Data and select the OData Feed.
(or New Source if you are already in the Query Editor)
Type in the URL and it will only retrieve the data you specified.
There are some details on MSDN on the filters that you can apply in OData URLs.
That part is simple enough, but how did I create that URL?
Use the OData Generator Plugin
If you are not excited about the prospect of creating those URLs manually, then there is an awesome plugin for the XRMToolbox that will do them for you.
It is called the OData Generator Plugin and has been made available by Nizar JLASSI.
You can download the OData Generator Plugin on CodePlex here.
At the time of writing the plugin is not available in the XRMToolBox Plugin Store. You will have to download the .dll file from the link and drop it in the plugins folder where you have your XRMToolBox.
Then open the plugin in the XRMToolBox.
Click on Load Entities and select the entity you want to work with. In this case, I am choosing the account entity.
Then click on Load Attributes and Relationships.
In the “Select” column, check all the fields you want to include. In the “Filter” column, check the fields you want to apply a filter to. In the box for the filtering attributes, I added the state field and the equal to “CA” operator and value.
It does take a few seconds for the fields to show up on the right side from when you click on them.
When done, click on Generate OData.
This will generate the OData URL with all the right parameters. Copy the URL and paste it into the OData Feed in Power BI.
Simple as that.
Tip: If you need to create a URL for a different entity, or need to go back and make changes, then I have had more success with starting over by closing the OData Generator and opening it again, rather than trying to use the clear buttons.
The OData Generator also have some additional features for expanding data from the relationships on the entity.
In Power BI, if you need to make adjustments to the records and the fields the OData URL is retrieving, you can modify the connection URL you created.
Open the Query Editor
Select the query on the left and make sure the applied step “Source” is selected on the right.
In the formula bar, the OData URL is then displayed. From here, you can either add or remove fields from the URL or change the filter. I would recommend only making minor modifications manually unless you are very familiar with the syntax. You can always go back to the XRMToolBox and the OData Generator Plugin and have it create a new URL to replace the one you have here.
You can use the OData URLs in conjunction with the standard OData connection to Dynamics 365 in Power BI. While I have not decided on a preferred method yet, it seems to be the easiest to use the standard OData connection to select most of the entities you need. Then add the entities that specifically require filtering using the approach detailed in this blog post.
Note: If you are using the Data Export Service to replicate your Dynamics 365 data to an Azure database, then you have other means of prefiltering data before it is pulled into Power BI, but that will have to be a different blog post.
That is it for this time. As always please sign up for my newsletter and follow me on Twitter. Follow @CRMChartGuy
10 thoughts on “Prefilter data for Power BI with OData URLs”
Is there a trick to getting this OData Generator plugin to show up in XRM Toolbox? I checked the unblock option and followed the code plex conversation on this but cannot get it to show up.
Hi Keith. Not sure if the OData generator is being updated any longer. However, the “FetchXML Builder” has a “Show OData equivalent” under the View button. Reminds me I need to update this post 🙂
Thanks will give that a try.
Hi Keith! I do no know if you managed to solve this issue. If I understand it correctly, you cannot see oData Generator plugin in XrmToolbox?
I did this with latest version of XrmToolbox (v1.2018.1.20) and did not try with previous versions. You have to manually copy the dll from codeplex site (odatageneratorplugin.codeplex.com) into the plugins folder of XrmToolBox and restart application in case you have it opened.
Then plugin should be visible in list.
I hope this will help!
Slavko, I was able to use the Plugin Urlik mentioned to accomplish what I needed. Thanks for the help however as now I know how to do that.
@Ulrick, sweet! Thank you! Do you have any examples using a parameter, e.g., I’d like to filter to an entity where the modifiedon date is fed by a parameter. I’ve figured out how to feed it a date in the filter string, but not from a parameter value yet.
I do not, but that sounds like a good idea.
This may work for CRM/CE , but against 365 FO both $select and $filter are ignored.
Im also having this problem, although this post is very old.
Any solution for Finance and Operations to limit the amount of data pulled into Power BI other than filtering it out in the steps?
I am not having any luck in writing a pre-filter or pre-filters for an OData feed in Power BI. Has the language or process changed? It seems so simple, but doesn’t work. Appreciate updating this page as this is one of the few places addressing pre-filters with OData in Power BI!