-
-
Save d4hines/b5d9900fc1ea9d26311d2145505837cb to your computer and use it in GitHub Desktop.
| /* | |
| 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 |
@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.
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!