Mapping your data within Excel through PowerView

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
here.

Getting Ready…

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:

  1. Open up Excel, and from the Dynamics AX ribbon bar, click on the Add Data button and select the Add Tables option.
  2. Search for the LogisticsPostalAddress table, and select the table. When you have done that, click on the OK button.
  3. 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.
  4. Click on the Fields button in the Dynamics AX ribbon bar to return to query mode.
  5. 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:

  1. 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.
  2. 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: