Getting Data via FetchXML from Dynamics

[Lisi]

Hi, I'm trying to get data via FetchXML into PowerBI. I followed the steps described in Use FetchXML in Power BI with Dynamics 365 customer engagement | crm chart guy. 
I tried to connect but I always get a message, that the connection ist not possible because the string contains invalid characters. 

The generated URL is: https://xxxx.api.crm4.dynamics.com/api/data/v9.1/accounts?fetchXML=%3Cfetch+version%3D%221.0%22+outp...

I changed the first part of the URL to publish here. Does anybody have any ideas? 

Thanks a lot in advance, Lisi

[Lisi · Accepted solution]

Solution see: https://crmchartguy.com/2018/07/08/power-query-builder-updated-now-supports-fetchxml-to-get-your-dyn... 

[Anonymous]

Hi @Lisi ,

Based on this document——How to handle Special Characters in Fetch XML | Microsoft Dynamics 365 CRM Tips and Tricks . Fetch XML is the easiest way to write complex queries to retrieve data by joining multiple entities.

We could use webutility.htmlencode(uiname) to handle such an error. For example:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daryl-Lynch-Bzy]

Hi @Anonymous  - the following "Fetch XML is the easiest way to write complex queries to retrieve data by joining multiple entities" may be true, but Power BI would prefer a Star Schema rather than One Big Table.

@Lisi - I wondering do you need to separate the URL and Relative Path to get the FetchXML to work.  Chris describe this in the Blog - Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code Chris Webb's BI Blog (crossjoin.co.uk)

[Lisi]

Hi Daryl, if I get the data the usual way I have to import all kinds of tables and lot's of data. FetchXML would give me the chance to import a smaller amount of data from more than one entity. It would be more like importing a view from sql Server. Or is there another way? Thanks, Lisi

[Daryl-Lynch-Bzy]

HI @Lisi - I would like understand why the standard D365 in Power BI cannot be used?  Why do you need to use the FetchXML?