In last post, we have seen how to generate the fetchxml which can be used in Power BI for fetching the data from Dynamics 365 CE views. However the basic question of how to connect is still not discussed :). Let us do that connection in this post.

Let us again go to XRM tool box and open Power Query(M) Builder and click on “Generate Service Url”. As the plugin is already connected with the target CRM, it will generate the service urls which can be used in Power BI for connectivity.

We can see the two tabs under Service URLs

Dyn365CEBaseURL: is the actual URL which we use in browser to open the CRM

ServiceRoot URL: is API URL as shown in  below snippet.

Once we have these URLs, let us create the blank queries in Power BI desktop. Click on Getdata and click on Blank query.

Go to Advanced Editor

Copy the Dyn365CEBaseURL generated above and paste in advanced editor

Rename the Query as Dyn365CEBaseURL

Similarly create one more blank query and name it as ServiceRootURL and paste the ServicerootURL created in above steps in advanced editor.

We have both the queries ready for the data connectivity. Now let us create few more queries for fetching actual data.

Create one more blank query and paste the fetchxml generated in earlier post in the advanced editor. I have named the query as Opportunity View.

On Similar lines we can go ahead and add additional queries for different data , for ex Account data , I have added Account view.

Once we add these views, Power BI desktop might ask  to re authenticate and ask to enter the credentials. Once we connect and create these basic queries, we can go ahead and create various reports as usual in Power BI.

Refer my earlier posts to know more about data modelling, creating reports and dashboards

Continue reading “Power BI and D365 CE Connectivity – part 2” →