Last active
October 18, 2018 17:35
-
-
Save d4hines/b5d9900fc1ea9d26311d2145505837cb to your computer and use it in GitHub Desktop.
PowerQuery (M) code to download saved Views/Advanced Finds from Dynamics CRM Web API (8.0 and up)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
FYI, special characters like "*" can break the query.