Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PowerQuery (M) code to download saved Views/Advanced Finds from Dynamics CRM Web API (8.0 and up)
/*
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
@d4hines

This comment has been minimized.

Copy link
Owner Author

d4hines commented Jun 7, 2017

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
@wlknsn

This comment has been minimized.

Copy link

wlknsn commented Dec 22, 2017

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

@d4hines

This comment has been minimized.

Copy link
Owner Author

d4hines commented Jan 2, 2018

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

@wlknsn

This comment has been minimized.

Copy link

wlknsn commented Feb 27, 2018

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!

@shelvinpv

This comment has been minimized.

Copy link

shelvinpv commented Mar 30, 2018

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.

@D2N2

This comment has been minimized.

Copy link

D2N2 commented Jul 23, 2018

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!

@d4hines

This comment has been minimized.

Copy link
Owner Author

d4hines commented Aug 21, 2018

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

@d4hines

This comment has been minimized.

Copy link
Owner Author

d4hines commented Aug 21, 2018

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

@d4hines

This comment has been minimized.

Copy link
Owner Author

d4hines commented Oct 18, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.