CODEWITHSUNDEEP
powerbidynamics-crm-2016
jasonscript
jasonscript

Reputation: 6178

Power BI connect to CRM 2016 Web API

I'm trying to use Power BI Desktop to connect to a CRM Online (2016 Spring Wave 1) instance using CRM's new Web API methods.

When I put my api into a browser like Chrome I get results back. For example if I use https://xxx.crm.dynamics.com/api/data/v8.0/my_records?$select=my_recordid I can see all the results being listed (in batches of 5000)

However, when I try the same thing in PowerBI I get an error telling me that a field already exists (see screenshot)

PowerBI error screenshot

I've seen some approaches where the URL is wrapped

= Json.Document(Web.Contents("<same url as above>")

but this doesn't seem like a good approach, and I don't know how to use this approach with paging.

So has anyone managed to get Power BI working with the new Web API calls?

Upvotes: 1

Views: 707

Answers (2)

Daniel Hines
Daniel Hines

Reputation: 63

I'm rather late to this question, but I've had good success with the "Json.Document(Web.Contents())" method. The trick to the paging issue was wrapping the call in a recursive function. For convenience, I've wrapped that recursive function such that I can pass in the name of a Saved View/Advanced find and get the results of that query.

As a Gist: https://gist.github.com/d4hines/b5d9900fc1ea9d26311d2145505837cb

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

GetQueryByName =
     //https://mycrm.mydomain.com/MYORG
    (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/contacts?userQuery=" & GetQueryByName(OrgUrl, QueryName, UserView),
return = Table.FromList(QueryAll(NamedQuery, null), Splitter.SplitByNothing(), null, null, ExtraValues.Error) 
in return

There are a bit more instructions on the gist if that helps. Hope it helps someone!

Upvotes: 0

jasonscript
jasonscript

Reputation: 6178

I created a new CRM Online Trial instance and retried using the WebAPI URL (https://xxx.crm.dynamics.com/api/data/v8.0/my_records?$select=my_recordid) in Power BI and this time it worked.

It must be something to do with the customisations that I have in place.

Also, I noticed that even though I included a $select=my_recordid filter in my WebAPI request, that PowerBI still loaded all columns names; however, only the column specified in my filter had values.

This would explain why the error occurs even when I specify a single attribute in the $select

Upvotes: 2

Related Questions