One of the easiest ways to map your Dynamics AX data is through PowerView directly from within Excel. PowerView will automatically recognize geographic columns as mappable data elements, and will translate them to the map view.
NOTE: This is part of the Using PowerBI to Analyze Dynamics AX Data blueprint series where we show how to use PowerView, PowerPivot & Power Map to analyze and visualize your data more effectively. If you want to see all of the other posts in this series, click
First we need to get some data from Dynamics AX that we can map. You can export the information directly form Dynamics AX, or query the data from Excel using the Dynamics AX Add-In. To do the latter follow these steps:
- Open up Excel, and from the Dynamics AX ribbon bar, click on the Add Data button and select the Add Tables option.
- Search for the LogisticsPostalAddress table, and select the table. When you have done that, click on the OK button.
- When you are returned to Excel, add the Street, City, State and Zip/postal code fields to the worksheet by dragging them over from the field chooser on the left into the worksheet table.
- Click on the Fields button in the Dynamics AX ribbon bar to return to query mode.
- Then click on the Refresh button in the Dynamics AX ribbon bar to query all of the data from Dynamics AX.
How to do it…
To use PowerView to show your Dynamics AX data within a Map view, follow these steps:
- Now that you have Dynamics AX data to visualize, click on the Power View button within the Reports group of the Insert ribbon bar.
This will convert all of the queried data into a data source and open up a new worksheet with the PowerView designer.
- Remove all of the default fields except for the City and the Location ID from the worksheet, and then click on the Map button in the Switch Visualizations group of the Design ribbon bar.
How it Works…
This will convert the view to a map view and all of your address records will be mapped based on the City.