Filter Your Power Query Data with CRM REST Builder
f 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
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!