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.
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.
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.
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.
1) Open Excel
2) Under the Data tab, select "Get Data", "From Online Services" and "From Dynamics 365 (online)"
3) Use Web API URL.
4) Sign in.
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.
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.
7) Edit Power Query.
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.
