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.
- The Service Root URL that you would otherwise use in Power BI https://MYCRMORG.api.crm.dynamics.com/api/data/v8.2/
- The PLURAL name of the entity schemaname follow by ?fetchXml=
- The FetchXML you downloaded from Advanced Find encoded as a URL
- Type in “Prefer” – it is not option you can select
- 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.
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
10 thoughts on “Use FetchXML in Power BI with Dynamics 365 customer engagement”
Hi , I am getting issues with this , its throwing an error below:
Details: “Specified value has invalid Control characters.
Parameter name: value”
Hi Rishi. Please check that you are typing the information rather than copying and pasting from the blog. The latter will cause some issues with the quotation marks.
Also, double check the versioning of the api as that has updated from 8.2 to 9.0 since this post was written.
However, I would recommend using the Power Query Builder ion the XrmToolBox to auto generate these. Check out the Power Query Builder page on this blog.
How to get more than 5000 record using Fetch-XML in Power BI.
Hi Sudhir. You use the Power Query Builder in XrmToolBox https://crmchartguy.com/power-query-builder/
Long FetchXML URL .. is there any way around this limitation in Power Query Builder?
Hi Brandon – I assuming you are thinking of an error that happens when your FetchXML Query gets too long and D365 won’t accept it. This happens when you have selected a lot of fields, or have very long field names. If that is the case, mark the “All Attributes” checkbox when creating your Power Query code. It’s next to the Link checkbox. This will replace querying the individual fields with and retrieve everything on the entity. The Power Query then only expands the fields that you requested from the full table.
[…] has a great post on this, and all credit to him for the following. (You may want to read his post in entirety, as I have summarized some […]
Hi, I managed to create a PBI panel using your guide (I apreciate your excellent article) so I published it and now I’m unable to update the data without a gateway. If the information come from Office365 why I can’t update it from web?
PS.: My authentication is by oganizational account (sorry don’t know how it is in English, it opens a web authentication in my company’s domain), could be that? Should I use a gateway?
[…] In a simple RLS scenario, we would be sourcing our data using OFeed but in order to get Access Levels from Dynamics we are going to use FetchXML. To see how to use FetchXML, click here. […]
This post saved my life!!!