How to Connect Excel to Dynamics 365 with Power Query

Alternative desc

Using Power Query in Excel, users can bring Dynamics 365 data into their Excel data model and easily prepare their data for analysis. In this article, we discuss how you can connect D365 to Excel using Microsoft Power Query and why we prefer Power Query over some of the other methods available.

What is Microsoft Power Query?

Microsoft Power Query is a connector that allows you to extract and transform data from hundreds of data sources including Dynamics 365, Azure SQL database, SharePoint, Databricks and more.

Who Is This Tool a Good Fit For?

If you have experience using Power Query, using it in Excel will be a breeze. Even if you have never used Power Query, the Excel add-in is easy use when trying to get simple data queries flowing into Excel. The tool is designed to support users all along the spectrums of technical expertise and data complexity.

Why Use Power Query vs. Another Method of Connecting D365 Data

Real-time Data

 

You’re likely aware of other methods for utilizing data from Dynamics 365 in Excel (opening D365 data in Excel Online, downloading a static worksheet, etc.), but many of them will only give you a snapshot—a image of what that data looked like at that moment. The Power Query Excel connector allows you to live-sync your data from D365, meaning that you have access to the most up-to-date information with the push of a button (or automatically upon opening Excel).


Data Transformation Options and Robust Query Engine

 

This connector is also valuable for its breadth of data transformation options. Power Query is a robust data modeling tool that allows users to bring in CRM data and model it in a way that makes sense for their business. You can relate tables, add columns, create calculated columns and more all before you start doing any work in Excel.

How to Connect Excel to Dynamics 365 Data Using Power Query

1) Open Excel

 

2) Under the Data tab, select "Get Data", "From Online Services" and "From Dynamics 365 (online)"

Alternative desc

3) Use Web API URL.

 

4) Sign in.

Alternative desc

5) The save button is no longer visible in the bottom right-hand corner of the record. You can tell if a record is saved or has unsaved changes to the right of the record name.

Alternative desc

6) The “open in a new window” button is back! While a similar feature was available in a previous version of Dynamics 365, it was missing from the most recent. Use it to pop a record open in a new window.

Alternative desc
Alternative desc

7) Edit Power Query.

Alternative desc
Alternative desc

After that, the data can be used in Excel by adding to tables, creating pivot tables, charts etc.

Would you like to learn more about connecting Excel data to Dynamics 365 or Power query? Read the Microsoft documentation or get in touch with an Altriva Solutions consultant.

Other articles you might be interested in...

FacebookTwitterLinkedIn