Analyze Dynamics CRM data using Excel PowerQuery and workbook

Source: Internet
Author: User

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!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.