PowerQuery (M) code to download saved Views/Advanced Finds from Dynamics CRM Web API (8.0 and up)

QueryByAdvancedFind.pq

/*
	Downloads Saved View/Advanced Find via Web API
	Expand All Columns to access the returned results. Columns prefixed "OData.Community.Display.V1.FormattedValue" show the properly formatted values.
*/
(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

Parameters:

• OrgUrl - e.g https://mycrm.mydomain.edu/MYCRMORG
• QueryName - e.g MyNamedAdvancedFind
• LogicalCollectionName - Plural of LogicalName e.g "contacts"
• UserView - If User view, True; if System view, False

Hi,

This works great! But given you are very reliant on the views (as in, they do not get changed or removed), I'm wondering whether this can be done with a query (fetchxml or webquery) directly? - I haven't found anything regarding this.

Regards

Hi wlknsn!
Yes, you can definitely execute FetchXML or OData queries with the WebAPI, and you can therefore do so through PowerQuery. However, I specifically wanted to separate the concerns of building the report from the details of the filters on the data going into the report. Moreover, with this setup, our non-technical business analysts can create an Advanced FInd in the CRM UI, and then just enter the name of it in this function and get their data like magic.

If you'd rather keep the query in the report, the NamedQuery expression is where the URL to retrieve the data from is built up. All you need to do is build a different URL and pass it into the QueryAll function, which recursively sends GET requests until there are no more results to fetch.

If you wanted to execute FetchXML, you would instead do something like: '/api/data/v8.0/yourlogicalentity?fetchXML="<YourFetchXML/>", as described here

If you wanted to use the standard OData query operators (which in my opinion are easier and less verbose than FetchXML, although admittedly less flexible) you would build up the query string as described here.

Hope this helps!
God bless

Tried all that now, but then you run into 5000 record limitation. And just found out that by building URL, time related filtering is not supported in Dynamics 8.2, so your solution seems to be the only way (unfortunately) => but a good one that is!

Hi
first of all a big thank you for the solution. I was able to use this and get more than 5000 records. But when i publish this to online power bi I am unable to schedule refresh of the report. I have another report where i am not using this function to create the report directly using web api and it works fine. I am also able to refresh the data from Desktop version of power bi. Seems some issue with online version while using the function.
Can you please help me how to schedule automatic refresh on online Power BI.

Thank you for the code! This was very useful as the regular fetchXML or Dynamics connector did not load all the records and values (codes instead of names). I'm an analyst and don't have coding skills so this helped me progress a little with the analysis.
However, not all the columns saved in my view are loaded. Any ideas why that is (maybe there a limit on the number of columns or chosen columns are in related tables) and how to fix it?
Thanks in advance!

@shelvinpv, is your instance of CRM an On Premise installation? If so, it's only accessible on your local network (unless it's IFD). In that case, you'll have to install the Data Gateway for Power BI. I'm not an expert in that, but I'll ask our analyst who is if he has any advice.

@D2N2, I've had this very problem as well. It seems like PQ randomly drops some columns, and I can never get them to load. It seems to be related to joining across multiple entitites. I noticed it pulls in the alias from the fetchxml as a prefix, which is very long - I wonder if the length of the field name is triggering a bug in PQ or something. I don't know any workarounds, and any insights anyone else has would be greatly appreciated.

FYI, special characters like "*" can break the query.