Up until now we have just been looking at the data from one data table within Dynamics AX. If you want to merge multiple tables together, then you don’t have to resort to using SQL statements, or building complicated queries, you can use PowerQuery to merge the data for you.
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
For this example we will need to add in some more tables into the workbook so that we can link the postal addresses with the customer accounts. To do this, follow these steps:
- Click on the Add Data button in the Dynamics AX ribbon bar, and selecting the Add Tables option.
- When the table selection dialog is displayed, add the CustTable and the DirPartyLocation tables to the selected tables, and click the OK button.
- When you return to Excel, click on the Fields button in the Dynamics AX ribbon bar to exit design mode.
- Then click on the Refresh button in the Dynamics AX Ribbon bar to refresh the data in the table queries.
How to Do It…
To use PowerQuery to merge data, follow these steps:
- To use PowerQuery to combine multiple sets of data into one dataset for reporting, follow these steps:
- Select the CustTable table and then click on the From Table button within the Excel Data group of the Power Query ribbon bar to turn it into a query. Then click the Done button.
- Also select the DirPartyLocation table and then click on the From Table button within the Excel Data group of the Power Query ribbon bar to turn it into a query. Then click the Done button.
- To merge the DirPartyLocation and CustTable queries, select the DirPartyLocation query table within the workbook, and then click on the Merge button within the Combine group of the Query ribbon bar.
- When the Merge dialog box is displayed, select the CustTable query as the child table and then select the two columns that you want to match the tables on – in this case the PartyID. When you have done that, click on the OK button to return back to the query builder.
- Your new merged query will show up as a new worksheet, but the columns from the CustTable are not showing up.
- Click on the NewColumn that was created, check the fields that you want to show in the new query, and then click OK.
- Now you will see all of the columns from the CustTable showing up in the query. When you are done, click on the Done button to return to the Excel workbook.
- We now want to take this link one step further, and merge the new query with the postal address so that we can see the customers with their appropriate address information. So select the query that you just created, and then click the Merge button again. This time, for the child form, select the LogisticsPostalAddress query and then link the two queries by the LocationID column.
- When the new query is displayed, expand the NewColumn and select all of the address columns from the LogisticsPostalAddress table.
- Just to make everything tidy, double-click on the header of the query, and you will be able to rename the query to be something more descriptive, and then click on the Done button to return to the Excel workbook.
How it Works…
Now you will see a merged query of the three tables.
If we select the query, and click on the Map option within the PowerMap group of the Insert ribbon bar. Then select the Launch PowerMap option to open up PowerMap and we will see all of the new fields that are available for reporting.