Analyze Dynamics CRM data using Excel PowerQuery and workbook

Analyze Dynamics CRM data using Excel PowerQuery and workbook

You may also find that most of the articles written by bloggers are related to developers, involving some programming knowledge that requires professional programming knowledge. Today, I will introduce some different things to you and tell you how to use Excel to analyze data in the Dynamics CRM system.

Everyone knows about the Excel software. If you don't know anything about it, you can go to the wall. Microsoft does not need to pay attention to the Excel software. Its position in the Office product suite is also one of the best, with the recent versions of Release, it is easy to use Excel to display some cool BI results!

In this article, the bloggers will use Excel 2016 and Dynamics CRM 2016 Online for demonstration. If you want to learn the examples in this article, you need to prepare the corresponding software first. Here, we need to use two heavyweight functions in Excel 2016, PowerQuery and workbook.

Scenario

First, capture some customer records in CRM, then capture some business opportunity records in CRM, associate these two data sources in Excel, and finally display them in Excel.

Preparations

In this example, I plan to use PowerQuery to call Dynamics CRM Odata Endpoint and obtain the expected data. How to construct OData Query is involved here. In this example, I used the following two OData queries:

 

Account Data Source:https://ghostbear.api.crm6.dynamics.com/xrmservices/2011/organizationdata.svc/AccountSet?$select=AccountId,Name,AccountNumber,OwnerId,Address1_Country,Address1_City,Address1_Line1Opportunity Data Source:https://ghostbear.api.crm6.dynamics.com/xrmservices/2011/organizationdata.svc/OpportunitySet?$select=OpportunityId,Name,ParentAccountId,BudgetAmount,StepName

Then, the customer data is the primary data, the business data is the slave data, and the customer data is displayed in a PivotTable manner. Procedure

To use PowerQuery to obtain the Dynamics CRM data source, enter the authentication information of CRM.

Enter CRM verification information

Successfully loaded CRM Data Source

Load the PowerQuery data source into the workbook model: select a data table to be preloaded in Excel, and click "Add to data model"

 

Drag a field to create a table's master-slave relationship and insert the created data model to Excel.

Configure charts for displaying the dataset Data Model

Click Refresh data. PowerQuery automatically obtains the latest system data.

Is it cool? In this way, end users can make powerful reports! Please try it!