Export D365 Web API Response to Excel

Introduction:

The Dynamics 365/Dataverse Web API is one of two web services to work with data and table and column definitions in Dataverse. The other is the Organization Service.

The Dataverse Web API provides a development experience that can be used across a wide variety of programming languages, platforms, and devices. The Web API implements the OData (Open Data Protocol), version 4.0, an OASIS standard for building and consuming RESTful APIs over rich data sources.

Because the Web API is built on open standards, we don't provide assemblies for a specific developer experience. You can compose HTTP requests for specific operations or use third-party libraries to generate classes for whatever language or platform you want. 

Requirements

A common task in CRM environments is using the D365 Web API to query data and export for further analysis and reporting.

For example:

We can query all EntityDefinitions through the API but the response is in JSON format, and it's not easy to do further analysis and reporting.

One option to achieve this goal is to export the API results to Excel. In this blog post, we will provide step-by-step instructions on how to accomplish this task.

Step 1: Identify the URL of the Dynamics 365 Web API

The first step is to identify the URL of the D365 Web API.

This URL usually follows this formhttps://yourcrmorg.crm.dynamics.com/api/data/v9.1/EntityDefinitions. Replace "yourcrmorg" with your actual organization name, "v9.1" with the version number of the API you are using (e.g., v9.0), and "entitysetname" with the name of the entity you want to export data from.

Step 2: Open Excel and navigate to the "Data" tab. Follow the step 1,2,3 as shown below:

Input the username and password.

Then in the dialog box below, enter the URL for your D365 Web API and hit "OK":

Excel will display a preview of the table in the API response. Click "Transform Data" to import the data into a new worksheet:

Step 3: Transform the Data with Power Query is a powerful data transformation tool embedded within Excel. With Power Query, you can filter and clean up the D365 Web API response to meet your needs. 

Within Power Query, you can apply various transformations to the data with ease. For example, to filter out unwanted columns, simply right-click on the column header and choose "Remove":

Expended the Record Type Column

Step 4: Close & Load

Step 4: Refresh the Data Since the D365 Web API data is dynamic by nature, you may want to refresh the data in Excel periodically to display the latest information. To do so, click "Refresh All" under the "Data" tab:

Conclusion:

Exporting data from Dynamics 365 to Excel can be easily accomplished using the Web API and the built-in tools of Excel. By following the simple steps outlined in this blog post, you should be able to quickly export your data for further analysis and reporting.

The End