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

CONTENTS

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.

The system shows the customer information based on requested columns.

Click HERE to read the full post.