Power Query 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 OData connector. For our purposes, we will be using data that we gather from a variety of sources that we transform using Power Query and then render in Power View using Excel 2016. This article is an update to 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 the data from document management using OData, import external data, merge the data, create a calculated column, and finally display using Power View.  As a bonus, we will also render this information in Power BI.

This is part four of a four-part series on this topic.  Parts one and two worked with Power Pivot.  Part three worked with Power Query, Power View and AX 2012. This one completes the series by using Power Query, Power View and 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 ODATA

We will need to get our data from Dynamics 365 for Finance and Operations, Enterprise edition and in this case, we will be using data from an OData data source. For this process, we will connect to an existing query called CustInvoiceSRS that has a custom filter called USSalesUSMF2011.  Please see my earlier article on AX 2012 Power Pivot for instructions to create an OData feed if none exists using Document Management.

To connect to an OData feed:

1. Open Excel.
2. Click the Data
3. Click New Query in the Get & Transform
4. Click From Other Sources.
5. Click From OData feed.
6. URL, enter:

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

7. Click OK.
8. If asked for authentication, use organizational credentials from AAD to connect.
9. Choose SalesInvoiceHeaders.
10. Click Edit to use Power Query to filter the data.
    • Isolate transactions in US Dollars – click the CurrencyCode column drop-down and select only USD. Rename to Currency.
    • Remove the transactions don’t have an identified state. – click InvoiceAddressState and unselect (blank). Rename to State.
    • Rename TotalInvoiceAmount to InvoiceAmount.
    • Remove columns that aren’t needed – Select the Currency and Invoice Amount Right-click the selection and then click Remove Other Columns.
11. Click Close & Load. The data is added to your workbook in a new tab.

Importing External Data

One of the key ways to explore new data sets is to find information that is external that can be used for summary purposes or to update the data.  In this case, we will be using the population data from Wikipedia and abbreviation data from a website.

To add external data:

1. In Excel, click Data tab > New Query > From Other Data Sources > From Web.
2. In the URL enter:

https://en.wikipedia.org/wiki/List_of_U.S._states_and_territories_by_population

3. Click OK.
4. Select Population of states, territories, divisions and regions.
5. Click Edit to use Power Query to filter the data.
6. Rename the State/Territory/Division/Region column to State.
7. Rename the 2013 column to Population. Set the Data type to Whole Number.
8. Select State and Population, right click and Remove Other Columns.
9. Click Remove errors to eliminate areas without populations listed.
10. Click Close & Load. The data is added to your workbook in a new tab.
11. Click Data tab > New Query > From Other Data Sources > From Web.
12. In the URL enter:

www.50states.com/abbreviations.htm

13. Click OK.
14. Select State Abbreviations.
15. Click Load.
16. The data is added to your workbook in a new tab.

Merge data and calculate

We are now ready to merge the data and create a calculation.

To merge the data.

1. In Excel, starting with Sheet2 that contains the Sales data, select the table.
2. Under Query Tools on the ribbon, select Query > Merge.
3. Select State Abbreviations as the second table.
4. Select columns State and Abbreviation:
5. Click OK.
6. Click NewColumn double arrow, unselect Abbreviation > Click OK.
7. Rename the new column State Name.
8. Click Close & Load. The data is added to your workbook in a new tab.
9. Click Query > Merge.
10. Select Population of states, territories, division… for second table.
11. Click OK.
12. Click NewColumn double arrow, unselect State > Click OK.
13. Rename the new column Population.
14. Click Add Column.
15. Click Custom Column.
16. Enter

100*([InvoiceAmount]/[Population])

17. Click OK.
18. Rename the column to Sales per capita.
19. Click Home
20. Click Close & Load. The data is added to your workbook in a new tab.
21. Save your Excel file, if you haven’t already.

Display using Power View and Power BI

Now our data set is ready for us to render the information. The goal is to better understand the information graphically so that we can make inferences or decisions.

To graph data.

1. In Excel, starting with Sheet6 that contains our master data set. Select the table.
2. Click Insert
3. Click Power View. If no Power View is available, use the Help to configure the button as it varies by version.
4. Select the newly created table.
5. Click Other Chart in the Switch Visualization group.
6. Select Scatter. Expand to fit page. Here is an example of what it will look like:
7. Title your work.
8. Save.
9. In Power BI, click Get Data.
10. Under Files, click Get.
11. Local File.
12. Select the file you created earlier.
13. Click Open.
14. Click Upload.
15. Under Workbooks, open the file and review.

This article explored the process of using Dynamics 365 for Finance and Operations, Enterprise edition to visualize data within a Pivot View, which can be easily be translated to a Power BI dashboard. 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 AX2012 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 AX2012 and Dynamics 365 for Finance and Operations, Enterprise edition.

by Shaun McMikle for RSM

Shaun McMikle