Power BI and D365 CE Connectivity – part 1

It’s been long time and I was looking for writing something about Power BI and Dynamics 365 CE connectivity. In my earlier posts we have seen connectivity between Power BI and Microsoft Navision using content packs. Its high time to discuss about the Dynamics 365 CE and Power BI.

There is a very basic way of connecting to Dynamics 365 CE from Get Data functionality where I can select the D365 online and directly select the entity to which to connect to. However practically it may not be useful to connect to single entities at a time and create entire data model from scratch.

As D365 CE technical associates or even functional associates, we are very comfortable with fetchxml and “Advanced Find”. Hence we it would be easy for us to create required views in D365 to get specific data from CRM entities and the use those in Power BI for reporting or dashboards. Sounds interesting hmm 🙂 So lets see how to achieve that.

Again XrmToolBox comes to help us, one of the favorite tool as CRM consultants! We just need to install Power Query Builder plugin as shown in below snippet

We can just search with word “Power” and get this plugin. We have to install this plugin and open this plugin connected with the target D365 CRM organisation.

Once this plugin is connected with this target CRM organisation, open the plugin and click on Load entities as shown in below snippet

It would list all the entities in that CRM instance and we can search required entity as shown in below snippet.

Let us search opportunity entity here and look at the views for this entity

Let us select the All Opportunities view for learning purpose. We can actually create complex views based on our requirements to the extent of CRM out of box capabilities.

Opportunity view — All Opportunities view

As soon as we select the view, the below section showing the list of fields would be refreshed and fields selected in the view would be shown in right bottom section. We can still go ahead and add /remove fields as required using the buttons ==> or <==. Now lets generate the fecthxml for this so click on Update fetchxml button.

We will get list of fields in the fetchxml as shown below

Let us check the checkbox Add record URL to get the record url of the record so that if user wants to navigate to the particular record, this url can be used .