Power Pivot Data Exploration in Excel for Microsoft Dynamics for Finance and Operations, Enterprise edition

One of the continuing ways to extract data from Microsoft Dynamics 365 for Finance and Operation, Enterprise edition is to use the export function. For our purposes, we will be using data that we render using a Pivot Table in Microsoft Excel 2016. This article is based on an earlier TechNet series on the same topic for Microsoft Dynamics AX 2012; I have updated for the tools and menus that you see today. In this exercise, we will be extracting data from a list page into Excel, importing data into PowerPivot using an OData feed, and creating a Pivot Table from the output.

This is part two of a four-part series on this topic.  Part one was an updated walk-through of this process for AX 2012. Two of the series are on Dynamics AX 2012 and two are on Dynamics 365 for Finance and Operations, Enterprise edition.  Each time we are modeling the same tools only using different connections and a bit different data.  The purpose of this is to support both groups of implementations and to transition you quickly to the new Dynamics 365 for Finance and Operations, Enterprise edition tools.

Extracting Data from a List Page into Excel

We will need to get our data from Dynamics 365 for Finance and Operations, Enterprise edition. One of the best methods is to harvest data from a list page.  If you don’t have a list page that feeds this information, kindly ask your IT department to create one. Many of the existing list pages in Dynamics 365 for Finance and Operations, Enterprise edition do all that you would want by using personalization to find the information that you need.

To extract key data from a list page into Excel:

1. Open Dynamics Dynamics 365 for Finance and Operations, Enterprise edition.
2. Click the Show navigation pane
3. Select Accounts receivable > Customers > All customers.
4. Click Options, the last menu item. If not displayed, click the ellipses to see any menu items that are not shown.
5. Click Personalize this form.
6. Click the plus button.
7. Click on the Customer
8. Add the following fields from the Customers table:
   • Method of Payment
   • Segment
   • Subsegment
   • Customer classification group
9. Click Insert.
10. Close.
11. After the All customers list page is refreshed, click Export to Microsoft Excel in the Open in Office button.
12. Click Download.
13. Click Open if asked by Internet Explorer settings.
14. Click Enable Editing if challenged Protected View.
15. File > Save as > find the desktop or where you would like to place > File name CustomerInformation > Save.

Importing Data into Power Pivot by using OData

This has been simplified for Dynamics 365 for Finance and Operations.  With just a few clicks directly in the Excel 2016 Query Editor, we can access the information that we need for the demo.

To create an OData feed:

1. In the same Excel workbook you used earlier in this walkthrough, click the Data
2. Click Data
3. Click New Query.
4. Click From Other Sources > From OData Feed.
5. URL, enter:

https://<YOUR SERVER NAME HERE>.cloudax.dynamics.com/data

6. Click OK.
7. If asked for authentication, use organizational credentials from AAD to connect.
8. Choose CustomerPaymentJournalLines.
9. Click Load.

Creating a Pivot Table

We are now ready to render the data in a Pivot Table.  These steps will create a pivot table from the data.  With a small amount of alteration, you could also render this data using a Pivot Chart.

To create a sample report:

1. In Excel, click the PowerPivot
2. Add to Data Model.
3. Click OK.
4. Click on Sheet1.
5. Add to Data Model.
6. Click Home
7. Click Pivot Table.
8. Click OK.
9. Expand CustPaymentJournalLines and then select CreditAmount.
10. Expand Table and then select Segment.
11. Add PaymentMethodName to rows from CustomerPaymentJournal Lines and Add Name from AxTable to columns
12. Click Analyze
13. Select Insert TimeLine.
14. Choose TransactionDate
15. Click OK.
16. Use items to interact with.

This article explored the process of using Dynamics 365 for Finance and Operations, Enterprise edition to visualize data within a Pivot Table, which can be easily be translated to a Pivot Chart. In this exercise, we extracted the data from a list page into Excel, imported data into PowerPivot using an OData feed, and created a Pivot Table from the output.  Through the process of creating several different renderings of the data, you can quickly interact with the set of information and create inferences for decision making.

For more information, I would recommend that you attend our Dynamics AX 2012 or Dynamics 365 for Finance and Operations, Enterprise edition training in person or on-demand online to learn more.

Plus, keep an eye out for more articles that dive into the technical aspects of using Excel, X++ code, and PowerBI.  Please visit academy.rsmus.com for more information and training materials that will cover this and 100’s of other topics for AX 2012 and Dynamics 365 for Finance and Operations, Enterprise edition.

by Shaun McMikle for RSM

RSM Staff