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.

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.

Select USSalesUSMF2011.

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.

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.

Select Scatter. Expand to fit page. Here is an example of what it will look like:

Title your work.

Save.

In Power BI, click Get Data.

Under Files, click Get.

Local File.

Select the file you created earlier.

Click Open.

Click Upload.

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.