Power BI and D365 CE Connectivity – part 2

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.

Generate Service URl
Generate Service URLs

We can see the two tabs under Service URLs

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

Generate Service URl1

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

Generate Service URl2

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

Blank Query
Creating Blank Queries

Go to Advanced Editor

advanced Query editor
Advanced Editor

Copy the Dyn365CEBaseURL generated above and paste in advanced editor

advanced Query editor1
Paste Dyn365CEBaseURL
advanced Query editor2
Added Dyn365CEBaseURL

Rename the Query as Dyn365CEBaseURL

advanced Query editor3
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.

advanced Query editor4

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.

opportunity view editor
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”