I wasn’t really planning to write a post on this subject, but since this method helped me out significantly last week, I decided to write it up anyway.

So first up, why use FetchXML to get data into Power BI when there is already the OData connection available. Well, FetchXML will give more filtering options, but most importantly, it will also fetch the labels for option set values, owner names in lookup fields etc. That’s great, but it also limits you to a max of 5000 records.

I first encountered the use of FetchXML in the Sales Manager content pack from Microsoft. It mostly uses OData, but the Account table is using FetchXML. And it will limit you to a max of 5000 accounts in the content pack as well.

The content pack can be found here: https://technet.microsoft.com/en-us/library/mt490470.aspx

Retrieve data with FetchXML

First up is the FetchXML itself. As usual, the easiest way to create that is in Advanced Find and then download the code from there.

Here I created a view of active opportunities, made sure I included some fields with option sets and lookup values.

Download the FetchXML and remember where you saved it.

Go to your Power BI Dekstop application create new file and say Get Data.

Select the source called “Web”.

Click the radio button for Advanced and see the additional options.

For the different URL parts, now enter the following.

1. The Service Root URL that you would otherwise use in Power BI https://MYCRMORG.api.crm.dynamics.com/api/data/v8.2/
2. The PLURAL name of the entity schemaname follow by ?fetchXml=
   Example: opportunities?fetchXml=
3. The FetchXML you downloaded from Advanced Find encoded as a URL
4. Type in “Prefer” – it is not option you can select
5. Exactly type: odata.include-annotations=”OData.Community.Display.V1.FormattedValue”

Aside from step 3, this is simple text entry.

Update: In step 5, make sure you type the quotation marks to avoid formatting issues. Thank you Sarah Jelinek for pointing that out.

To get the FetchXML encoded as a URL, open the file downloaded from Advanced Find in notepad and copy the whole thing.

Go to a URL encoder site like https://www.freeformatter.com/url-encoder.html

Paste the FetchXML and hit encode.

Copy the URL Encoded FetchXML string.

Paste it in step 3 as mentioned above and click the OK button to continue

Open up the Query Editor

Click on List

Click on “To Table” in the Convert area.

Just click Ok to the prompt.

Now click the Expand button, the two arrows that points away from each on the one column you have in your table.

Now note that you are now expanding all the data retrieved from this table in Dynamics 365. That includes for example both the owner ID of the opportunity, but also the name of the owner. Similarly for the option sets, both the values and the labels are included. All the retrieved labels are available because of the Prefer odata.include-annotations=”OData.Community.Display.V1.FormattedValue” header we added when retrieving the data.

Now you can start renaming the columns to something more useful.

And you can of course always come back to the Applied Steps and see exactly what happened.

You can also come back to the Source step and paste a new FetchXML if you forgot to add some specific fields first time around. Just remember to go to the Expand step too and check the fields off there too. Generally not a good idea to remove fields from the FetchXML as it will cause issues in the following steps. You should also be able to update the FetchXML filter without any issues.

I want to mention a couple of blogs I found very helpful when looking into this.

This one from the CRM Chap: http://www.crmchap.co.uk/powerbi-deep-dive-using-the-web-api-to-query-dynamics-crm365-for-enterprise/

And this one from Achin: https://community.powerbi.com/t5/Community-Blog/CRM-Online-issues-with-oData-and-solution-with-FetchXML/ba-p/82702

The 5000-record limit

I have not been able to get around the 5000-record limitation. There are options for pagination in the FetchXML, however I have not figured out how I can include it with the above process in Power BI.

If anyone knows how, I’d love to hear from you and I would be happy amend this post with all the details.

The Power BI Query Accelerator

Now, if you don’t really feel like doing any of the above, you can always use the Power BI Query Accelerator from Sonoma Partners. It does the above, renames the fields and does not have the 5,000 record limit. All in all, pretty awesome and you can find it here.

https://www.sonomapartners.com/tools/powerbiqueryaccelerator

You will need to install their solution in your environment. Sometimes that’s not an option, which is why this more manual approach helped me out this past week.

It’s a solution you probably shouldn’t be using in a production environment or for very large data sets, but for demos and other things it is very handy. There’s a great conversation on the tool on the CRM Audio podcast here http://crmaudio.libsyn.com/power-bi-17-power-bi-data-query-accelerator-for-dynamics-365-with-keith-mescha

Hope you found this helpful and maybe found a new to get your data into Power BI for further analysis.

As always, please sign up for my newsletter and follow me on Twitter  Follow @CRMChartGuy