cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dimitrievski Frequent Visitor
Frequent Visitor

Dynamics CRM 365 data import in Power BI from view

Hello all,

 

I am trying to pull data from Dynamics CRM into Power BI. In CRM instance, there are views that contain exact data that I need to report on. I know that I can export to Excel and then pull in Power BI, but is there another way to pull that data via Web API? I tried to use default pbix template from Microsoft, but refresh is extremly slow and data pulled in does not exaclty fit my reporting purpose. 

What I am trying to do is pull data and match Oportunities to Goals in simple report. Goals are not even pulled in with default Microsoft pbix template for Dynamics CRM. 

 

Thanks for your help!

9 REPLIES 9
Super User
Super User

Re: Dynamics CRM 365 data import in Power BI from view

You can connect to the OData endpoint. You can use the CRM connector that is included in Power BI. But, I am not aware of any way to point it to a specific view, you will have to pare down the columns.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Moderator v-yuezhe-msft
Moderator

Re: Dynamics CRM 365 data import in Power BI from view

@dimitrievski,

Based on my research, views in Dynamics CRM are stored as FetchXML query. In Power BI, we can’t use the FetchXML query directly, we need to encode the FetchXML query to URL so that it can be used in Web source. For more details, please review the following blog.

https://community.powerbi.com/t5/Community-Blog/CRM-Online-issues-with-oData-and-solution-with-Fetch...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dimitrievski Frequent Visitor
Frequent Visitor

Re: Dynamics CRM 365 data import in Power BI from view

Hi All,

 

It looks like a very good solution using fetchxml, but when I try it, it gives me an error with credentials, Error is "Credential Type is not supported.", and I am using Organizational account with admin rights on the CRM instance. 

Moderator v-yuezhe-msft
Moderator

Re: Dynamics CRM 365 data import in Power BI from view

@dimitrievski,


Do you get errors when connecting to CRM in Power BI Desktop following the guide in this article? Would you please double check if the URL you entered in web source is correct?

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dimitrievski Frequent Visitor
Frequent Visitor

Re: Dynamics CRM 365 data import in Power BI from view

Hi, i am able to query both online and via pbix from desktop, it just doesn't work if i try to Get fromw web and i use fetchxtml generated link.
Moderator v-yuezhe-msft
Moderator

Re: Dynamics CRM 365 data import in Power BI from view

@dimitrievski,

Do you enter URL which is in format as <API address >/incidents?fetchXml=<URI we got>? How about you create the sample FetchXML query as descibed in the blog?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dimitrievski Frequent Visitor
Frequent Visitor

Re: Dynamics CRM 365 data import in Power BI from view

hi @v-yuezhe-msft,

 

I use a generated url to connect, it goes something like this

<API address>/Opportunity>fetchXml=%3Cfetch+version%3D%221.0%22+output-format%3D%22xml-platform%22+mapping%3D%22logical%22+distinct%3D%22false%22%3E%0D%0A*************%2Ffetch%3E
Moderator v-yuezhe-msft
Moderator

Re: Dynamics CRM 365 data import in Power BI from view

@dimitrievski,

Please check the last screenshot in that blog, the URL is formatted as <API address >/incidents?fetchXml=<URI we got>. 

Click “Get Data->Blank Query” to add a new blank query in Power BI Desktop, then paste the following code in Advanced Editor.

let
    Source = Json.Document(Web.Contents("<API address >/Opportunity?fetchXml=<URI we got>")) 
in
    Source 



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RV1 Regular Visitor
Regular Visitor

Re: Dynamics CRM 365 data import in Power BI from view

Doesn't fetch xml have a limitation of 5000 records? What do we do if we are dealing with a large dataset?