Problem Using Power BI with Dynamics CRM Web API

[d4hines]

Hello,

My question is right at the bridge between Power BI and Dynamics CRM. I thought I'd post here, but let me know if the CRM communities are better suited for this question. 

I've been very excited to start using the Dynamics CRM Web API with Power BI. My goal is to be able to define an Advanced Find (UserQuery) and use the Web API to pull that Advanced Find straight into Power BI, bypassing the 100K record limit of advanced finds and streamlining automated refreshes, etc. The function I wrote is successfully pulling all the records and returning it as a 1-column table of records, but when I try to expand those records into columns, not all the fields defined in the Advanced Find show up. At first I thought maybe it was a cache issue, or perhaps that Power BI was hitting some sort of size limitation, so I even tried defining each of the fields I was looking for manually in the M, but they just returned null.

Here's my M code: 

(OrgUrl as text, QueryName as text, LogicalCollectionName as text, UserView as logical) =>
let 

	GetQueryByName =
		(OrgUrl as text, QueryName as text, UserView as logical) => 
			let
				QueryType = if UserView then "user" else "saved"
				,return = OData.Feed(
						OrgUrl & "/api/data/v8.0/" & QueryType & "queries?$select="& QueryType & "queryid&$filter=name eq '" & QueryName & "'"
					)[userqueryid]{0}
		
			in
				return,
	QueryAll = 
		(nextURL, prev) => 
			let
				prevList = if prev <> null then prev else {},
				responseData = Json.Document(Web.Contents(nextURL, [Headers=[Prefer="odata.include-annotations=""OData.Community.Display.V1.FormattedValue"""]])),
				return = if responseData[#"@odata.nextLink"]? <> null then @QueryAll(responseData[#"@odata.nextLink"], prevList & responseData[value]) else responseData[value] & prevList
			in return,
	NamedQuery = OrgUrl & "/api/data/v8.0/" & LogicalCollectionName & "?userQuery=" & GetQueryByName(OrgUrl, QueryName, UserView),
	return = Table.FromList(QueryAll(NamedQuery, null), Splitter.SplitByNothing(), null, null, ExtraValues.Error) 
in return

And the FetchXML:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="elcn_academicprogramofinterestid" />
    <attribute name="createdon" />
    <attribute name="opportunityid" />
    <attribute name="parentcontactid" />
    <order attribute="elcn_academicprogramofinterestid" descending="true" />
    <link-entity name="contact" from="contactid" to="parentcontactid" alias="a_4e29b74a612a43f0969e0b8e08ce7e48">
      <attribute name="regent_firstcontactdate" />
      <attribute name="createdon" />
      <attribute name="regent_contactstatus" />
      <attribute name="datatel_prospectsourceid" />
      <attribute name="regent_leadsourcedetails" />
      <attribute name="regent_leadsource" />
      <filter type="and">
        <condition attribute="createdon" operator="this-fiscal-year" />
        <condition attribute="customertypecode" operator="eq" value="200000" />
        <condition attribute="statecode" operator="eq" value="0" />
      </filter>
    </link-entity>
  </entity>
</fetch>

More specifically, regent_leadsource and regent_leadsourcedetails aren't showing up as columns. 

Any ideas?

Many thanks,

Daniel Hines

[v-yuezhe-msft]

@d4hines,

I would recommend you contact Dynamics CRM support to check what fields are supported/included in the Web API.

Regards,

[d4hines · Accepted solution]

Thanks, they figured it out. Turns out null fields are not returned by default, and the top of my record set had a lot of null fields.