Dynamics 365 Customer Engagement Web API, Power BI & FetchXML Revisited

Functional consultants or administrators who have been using Dynamics CRM / Dynamics 365 Customer Engagement (D365CE) for any considerable length of time will likely have built up a solid collection of FetchXML queries, that are usable for a variety of different scenarios. Such privileged individuals are in the fortunate position of being able to leverage them in the following ways:

When building out Reports using SQL Server Data Tools and the Dynamics 365 Reporting Authoring Extensions.
Within tools like the XrmToolBox, when testing or running any example queries.
As the underlying queries for any bespoke views created using the SDK.

In other words, you have a range of useful queries that can potentially meet any needs within the application from a reporting standpoint. This is all well and good if you find yourself working solely within CRM/D365CE all the time, but when you start to bring in separate tools, such as Power BI, there can be some difficulty in migrating these across straightforwardly. Typically, you may find yourself staring down the barrel of a complicated and costly redevelopment exercise, where you have to invest a lot of time within Power Query to replicate your existing FetchXML queries as efficiently as possible; this puts potentially a lot of hard work and investment made into FetchXML query development down the drain almost immediately.

Fortunately, there is a way in which we can leverage our FetchXML queries using Power BI. I did a post on this very subject a few years ago, where I talked through an example from start to finish. The main limitations with this were, however, 1) the inability to return more than 5000 records at a time, given that paging was not correctly incorporated and 2) the fact that you had to manually define code for every query that you wished to utilise, which would take a lot of time to do and increase the risk of human error occurring.

As usual in these situations, the wonderful CRM/D365CE community has delivered a solution to address the first issue raised above. The Power Query (M) Builder tool is a handy plugin within the XrmToolBox that allows you to generate M query code snippets that you can use within Power BI Desktop. Most importantly, the tool incorporates a solution from Keith Mescha and the former Sonoma Partners Power BI Accelerator to get around the paging issue and allow you to return unlimited data from the application. You can find out more about the tool by checking out Ulrik “The CRM Chart Guy” Carlsson’s blog post dedicated to this very subject.

The tool is undoubtedly great, but if you have numerous FetchXML queries in a raw format that you wish to process within Power BI, it could take you some time to get these moved across into Power BI - particularly given that the tool does not currently support the ability to “bring your own” FetchXML queries. By using the example code provided by the tool, and carrying out some further work to address the second concern, it is possible to use the following M query function that will allow you to compartmentalise all of the above functionality in an easy to call Power Query function. Simply open a new blank query within Power Query and copy & paste the below into the window:

/*
    Generate FetchXML Query Results M Function
    Required Parameters:
        crmURL = The URL of your CRM/D365CE instance e.g. https://mycrm.crm11.dynamics.com
        entityName = The OData entity name that you are querying.
        query = The FetchXML query to execute. This should NOT include the top level <fetch> node, but only all subsequent nodes with double quotes escaped e.g. <entity name=""incident""><all-attributes /></entity>
    Credits: Big thanks to the Power Query Builder tool (https://crmchartguy.com/power-query-builder/) and Keith Mescha/Sonoma Partners Power BI Accelerator
             for figuring out the paging issue. Portions of the auto-generated code from the above tool is utilised within this function.
*/



let
    Func = (crmURL as text,entityName as text,query as text) =>
let
    FullURL = Text.Combine({crmURL, "/api/data/v9.1/", entityName},""),
    QueryAll = (z as text, x as number) =>
    let
                Source = Json.Document(Web.Contents(FullURL,
                        [
                            Headers=
                                [
                               #"Prefer"="odata.include-annotations=Microsoft.Dynamics.CRM.fetchxmlpagingcookie"
                                ],
                            Query=
                                [
                                fetchXml="<fetch distinct=""True"" page=""" & Text.From(x) & """ paging-cookie=""" & z & """>" & query & "</fetch>"
                                ]
                        ]
                    )
                ),
    Paging = try Xml.Document(Source[#"@Microsoft.Dynamics.CRM.fetchxmlpagingcookie"]) otherwise null,
    Retrieve = if Paging <> null 
                then List.Combine({Source[value],@QueryAll(Text.Replace(Text.Replace(Text.Replace(Uri.Parts("http://a.b?d=" & Uri.Parts("http://a.b?d=" & Paging{0}[Attributes]{1}[Value])[Query][d])[Query][d], ">", "&gt;"), "<", "&lt;"), """", "&quot;"), x + 1)})
                else Source[value]
    in 
        Retrieve,
    GenerateEmptyTable = (query as text) =>
    let
            XML = Xml.Document(query),
            #"Expanded Value" = Table.ExpandTableColumn(XML, "Value", {"Name", "Namespace", "Value", "Attributes"}, {"Value.Name", "Value.Namespace", "Value.Value", "Value.Attributes"}),
            #"Expanded Value.Value" = Table.ExpandTableColumn(#"Expanded Value", "Value.Value", {"Name", "Namespace", "Value", "Attributes"}, {"Value.Value.Name", "Value.Value.Namespace", "Value.Value.Value", "Value.Value.Attributes"}),
            #"Expanded Value.Attributes" = Table.ExpandTableColumn(#"Expanded Value.Value", "Value.Attributes", {"Name", "Namespace", "Value"}, {"Value.Attributes.Name", "Value.Attributes.Namespace", "Value.Attributes.Value"}),
            #"Filtered Rows" = Table.SelectRows(#"Expanded Value.Attributes", each ([Value.Attributes.Name] = "name")),
            #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Namespace", "Value.Name", "Value.Namespace", "Value.Value.Name", "Value.Value.Namespace", "Value.Value.Value", "Value.Value.Attributes", "Value.Attributes.Name", "Value.Attributes.Namespace", "Attributes"}),
            #"Transposed Table" = Table.Transpose(#"Removed Columns"),
            #"Promote Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
            #"Added Custom" = Table.AddColumn(#"Promote Headers", "@odata.etag", each ""),
            #"Reordered Columns" = Table.ReorderColumns(#"Added Custom", List.Sort(Table.ColumnNames(#"Added Custom"), Order.Ascending))
    in
        #"Reordered Columns",
    List = QueryAll("",1),
    Table = if List.IsEmpty(List)
        then GenerateEmptyTable(query)
        else #"D365CEData",
    #"D365CEData" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn( #"D365CEData", "Column1", Record.FieldNames(Table.Column(#"D365CEData", "Column1"){0})),
    D365CE = Table.ReorderColumns(Expand, List.Sort(Table.ColumnNames(Expand), Order.Ascending)),
    Results = if List.IsEmpty(List)
        then Table
        else D365CE
    in
        Results
    in 
        Func

When saved, Power BI will then generate a function that should resemble the below screenshot:

From here, you can then populate each of the required parameters as follows:

crmURL: This should be the full URL of your CRM/D365CE instance, e.g. https://myinstance.crm11.dynamics.com
entityName: Here the OData compatible entity name that you want to query should be specified. Note that this is not the same as the Entity logical name and, in most cases, the OData entity names will be plural values. For example, the Case entity (with logical name incident) becomes incidents when querying via the Web API. Guido Preite has done a great article that discusses this issue in greater detail and also how this may impact you when querying any custom entity data.
query: Within this field, you enter a portion of the FetchXML that you wish to use, specifically, the node and all subsequent nodes underneath this.

And then you are good to go! As an example, the following FetchXML query:

  <entity name="incident">
    <attribute name="title" />
    <attribute name="ticketnumber" />
    <attribute name="createdon" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <attribute name="casetypecode" />
    <order descending="false" attribute="title" />
    <filter type="and">
      <condition attribute="createdon" operator="this-year" />
      <condition attribute="casetypecode" operator="in">
        <value>2</value>
        <value>1</value>
      </condition>
    </filter>
  </entity>

Would return results similar to the below via the above function:

One limitation with this function, at present, is that I haven’t yet found a way to ensure formatted values return correctly, even when there are no results available. I’ll report back if I figure out a way to do this 🙂 A huge thanks to Keith, Ulrik and Sonoma Partners for kindly supplying the paging code snippet into the community and in helping me to build out the above function.