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!
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.
Proud to be a Datanaut!
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.
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.
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?
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?
I use a generated url to connect, it goes something like this
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