Use FetchXML in Power BI with Dynamics 365 customer engagement

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:

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.

Advanced Find

Download the FetchXML and remember where you saved it.

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

Web Source

Select the source called “Web”.

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

FRom Web Advanced options

For the different URL parts, now enter the following.

URL Parts

  1. The Service Root URL that you would otherwise use in Power BI
  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.

FetchXML in Notepad

Go to a URL encoder site like

Paste the FetchXML and hit encode.

URL encode step 1

Copy the URL Encoded FetchXML string.

URL Encoded step 2

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

Open up the Query Editor


Click on List

QE Click on List

Click on “To Table” in the Convert area.

QE Convert to Table

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.

QE Click Expand

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.

QE Applied Steps

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:

And this one from Achin:

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

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  

10 thoughts on “Use FetchXML in Power BI with Dynamics 365 customer engagement

  1. Hi , I am getting issues with this , its throwing an error below:
    Details: “Specified value has invalid Control characters.
    Parameter name: value”

    Please assist

    • 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.

    • 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.

  2. 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?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s