Power Query in Excel for Microsoft Dynamics AX 2012

One of the key legacy support items is extracting data from Microsoft Dynamics AX 2012. 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 Microsoft Excel 2013. This article is an update to an earlier TechNet series on the same topic; 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, importing external data, merging the data, creating a calculated column, and finally, displaying using Power View.  As a bonus, we will also render this information in Power BI.

This is part three of a four-part series on this topic.  Parts one and two worked with Power Pivot.  Two posts of the series are on Dynamics AX 2012 and two are on Microsoft Dynamics 365 for Finance and Operations, Enterprise edition.  Each time we are modeling the same tools but 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 AX 2012 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 Power Query
3. Click From Other Sources in the Get External Data
4. Click From OData Data feed.
5. In Data Feed Url enter:

http://<SERVER IP ADDRESS >:8101/DynamicsAX/Services/ODataQueryservice/

NOTE: The previous method was to use Server name; this is as not reliable.  This also assumes you have not reconfigured your AX client to a different port than the 8101.

6. Select USSalesUSMF2011.
7. Click Edit to use Power Query to filter the data.
   • Isolate transactions in US Dollars – click the CustInvoiceJour_CurrencyCode column drop-down and select only USD.
   • Remove the transactions that are not subject to tax – click the CustInvoiceTrans_TaxGroup column drop-down and clear No-Tax.
   • Remove columns that aren’t needed – Select the CustInvoiceTrans_TaxGroup and CustInvoiceTrans_LineAmount columns. Right-click the selection and then click Remove Other Columns.
8. Click Close and 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 Power Query tab > 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 and Load. The data is added to your workbook in a new tab.
11. Click Power Query tab > From Web.
12. In the URL enter:

www.50states.com/abbreviations.htm

13. Click OK.
14. Click Load.
15. Select State Abbreviations.
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 CustInvoiceTrans data, select the table.
2. Under Table Tools on the ribbon, select Query > Merge.
3. Select State Abbreviations as the second table.
4. Select columns CustInvoiceTrans_TaxGroup and Abbreviation:.
5. Click OK.
6. Click NewColumn double arrow, unselect Abbreviation > Click OK.
7. Rename the new column State.
8. Click Close and 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 Add Custom Column.
16. Enter

100*([CustInvoiceTrans_LineAmount]/[Population])

17. Click OK.
18. Rename the column to Sales per capita.
19. Click Home
20. Click Close and 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.
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 has explored the process of using AX 2012 data and external data by transforming it through the use of Power Query and rendering the finished product in Power View.  We also covered the process of adding this data set to Power BI. Through the process of creating several different renderings of the data, you can quickly interact with the 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