Filter Your Power Query Data with CRM REST Builder

If you haven’t played around with Power Query with Dynamics CRM yet you should really give it a try. For those familiar with Excel it provides some excellent reporting capabilities without the need to get involved with SSRS. One of the pitfalls I see is that by default it wants to download all records before applying the changes you make to the result set. If you aren't familiar, if you select Accounts it will download all fields for all Account records. If you then eliminate the columns you don’t need, the original OData query to CRM isn't updated to only retrieve that subset. I’m sure this can have its advantages but if you’re creating fairly specific reports you’ll likely not need all the data being downloaded. At the very least you’ll only want certain columns, and more than likely you’ll want to filter by some other criteria as well.

Here is an example of how much extra data we are talking about. Downloading all fields for 1,000 Accounts with minimal field population.



~1,913,890 X 20 = ~38.2 Megabytes of data

Elapsed time ~52 seconds

Here the request is limited by adjusting the OData query to 2 fields for the same 1,000 Accounts



~38,710 X 20 = 7.7 Megabytes

Elapsed time ~10 seconds

Needless to say this is considerably faster because of all the data we aren't downloading.

Limiting Your Query

I wrote the CRM REST Builder tool awhile back to generate JavaScript code for developers wanting to create advanced functionality using CRM’s OData endpoint. Along with JavaScript code it will also output the URL for Retrieve and RetrieveMultiple requests – these Urls can be used to target a specific subset of data in a Power Query report so you don’t need to first download all the data and then filter it after the fact.

1. Download and install the CRM REST Builder managed solution
2. Open the solution and it should bring you to a page where you can design your query
3. Select Retrieve Multiple
4. Choose the entity and fields you need to work with (can include related entities
5. Optionally apply filtering and ordering (should be self explanatory)
6. Select Create Request
7. Select Url
8. Copy the generated Url
9. In Power Query choose Dynamics CRM or OData Feed and use the generated Url instead of the generic CRM /OrganizationData.svc
10. Enjoy downloading only the data you need!