Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dimitrievski
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
dimitrievski
Frequent Visitor

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. 

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

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.

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

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

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

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

v-yuezhe-msft
Employee
Employee

@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.
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors