Another of the PowerBI tools is PowerQuery. This is an incredibly useful tool, because it allows you to query and massage data directly within Excel. This is especially useful if you only want to report off some of the data, or if you want to pre-filter the data.
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
How to do it…
To use PowerQuery to massage the data for reporting, follow these steps:
- Select the base data that we just queried from Dynamics AX, and then click on the From Table button within the Excel Data group of the Power Query ribbon bar.
This will open up a query editor window with your data from the original spreadsheet.
- If you right-mouse-click on any of the columns that you don’t necessarily want, you can select the Remove option to remove them from the query.
- If you select the drop down to the right of any of the columns, then a filter window will appear allowing you to select the values that you want to include within your query. If you click on the Text Filter item, then you can add additional filter criteria. Select the Equals option.
- This will open up a filter detail panel and you can select from the list of valid values. In this example we will select the USA country code and click on the OK button.
- Now our results will be filtered to just the USA addresses. If there are blanks in the records as there are in our example within the State column, you can select the filter option, and then just exclude any records with blanks.
- Once we have massaged the data, we can click on the Done button in the bottom right corner.
How it Works…
This will move the query results down to the Excel as a new worksheet.