Dynamics 365 & Office Integration Series - PART 2 - Connect to an OData feed through Get & Transform in Excel 2016

This article series is about integration practices between Dynamics 365 and office apps.  Integrations have been explained in 4 posts:

PART 1: Odata Queries Through Dynamics 365

PART 2: Connect to an OData feed through Get & Transform in Excel 2016

PART 3: Excel Integration

PART 4: Data Import/Export by Considering Your Business Purpose

Let's start PART 2

CONTENT Introduction Logging Creating Query

INTRODUCTION

Microsoft's office integration team spent their times for office integration as follows. 

%60 excel

%25 document management

%15 word and other things

So excel achieves several things with new technologies now. It's a great productivity driver for users.

Odata can be used to export data into excel - in order term, data can be imported to excel by using Get & Transform in Excel 2016. Excel 2016 includes a powerful new set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. Get & Transform enables you to connect, combine, and refine data sources to meet your analysis needs. These features are also used in Power BI, and in the Power Query Add-In available for previous versions of Excel.

Query Editor also lets you undo, redo, change the order, or modify any step… all so you can shape your view of the connected data just the way you want it.

We will be discussing here how Dynamics 365 is related to Get & Transform in Excel 2016.

LOGGING IN

To log in to Excel 2016 Get & Transform, use the Data tab in the ribbon, then select the New Query button from the Get & Transform ribbon group.

Note: If you are doing this the first time, you need to log in with your organizational account. 

Enter your credentials in the organizational account tab.

CREATING QUERY

To create a query in Excel 2016, use the Data tab in the ribbon, then select the New Query button from the Get & Transform ribbon group.

Select Odata Feed

https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers

Enter your Odata query in URL feed.

Main query is as follow

Instead of having all customer information here, we only want to see customer account and the customer name

https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers/?$select=Name%2CCustomerAccount

Prepare the data structure on query editor. Name the query.

You will be able to see the customers when you close the query editor.

System shows the customer information based on requested columns.

Dynamics 365 & Office Integration Series - PART 1 - Odata Queries Through Dynamics 365

This article series is about integration practices between Dynamics 365 and office apps.  Integrations have been explained in 4 posts:

PART 1: Odata Queries Through Dynamics 365

PART 2: Connect to an OData feed through Get & Transform in Excel 2016

PART 3: Excel Integration

PART 4: Data Import/Export by Considering Your Business Purpose

Let's get started with PART 1

CONTENT Introduction Some of Odata Query Structures Examples

INTRODUCTION

In Office Integration Series, articles will be providing information about how Dynamics 365 is integrated with the office app.

Dynamics 365 is giving an environment to work as productively as possible by reinventing productivity and business processes.  One of the ways is using Microsoft Office. Microsoft made office one of the pieces of the user interface for Dynamics 365. Some platforms Dynamics 365 is using are Odata and Azure blob storage.

Odata (Open Data Protocol): Data entities are table groups of top of the one or more tables. In other term, entities are a bit of views. They are used for import & export via Odata with the excel app and word app and document generation capabilities. Third-party applications can also use Odata. Odata represents the data in JSON formats. You can open JSON files by using notepad.

Azure blob storage: Not only something that is used for storing document attachments for document management, but also is used as a kind of transition between the system and the user. So when files get generated they get put into the azure blub storage and then the user gets navigated to those and browsers (chrome, internet explorer) deliver those to them.

SOME OF ODATA QUERY STRUCTURES

Odata queries: Type the following queries on your browser's address bar. Prepare your query in accordance with your environment's data.

Metadata - https://<environment>/data/$metadata

Data - https://<environment>/data/<EntitySet> e.g. Customers

Functions - https://<environment>/data/<EntitySet>/$count

In the next part of the article, we will query the following customers in different ways.

EXAMPLES

Let's see how queries work

Query - Metadata - https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/$metadata

This metadata feed is publicly accessible. But the following queries are not publicly accessible.

Query - Customers - https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers

Note that a JSON file has been prompted by the Odata service.

Save and open it with notepad for each private query.

This is the raw Odata feed for customers.

Since appearance is complicated, we can simplify  it in order to see only the customer names.

https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers/?$select=Name

We can also add another column to simplify customer name query. We need to put %2C before for each additional field. 

In order to add customer number, we put %2CCustomerAccount statement at the end of the query.

https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers/?$select=Name%2CCustomerAccount

We can do some interesting things as well. Let's count how many customers we have.

Query - Functions - https://usnconeboxax1aos.cloud.onebox.dynamics.com/data/Customers/$count

So system is able to represent the total number of customer account here as well.