When we left off on my last post we had been able to successfully use Power Query to grab data from our Microsoft Dynamics GP database and bring it into Excel with the purpose of building a dashboard.
So the first thing we need to do in order to build our dashboard is to setup a pivot table with the existing data we have. To accomplish this task hit CTRL + A and then hit ALT + D + P to create the pivot table. In keeping things simple select the defaults and hit the finish button.
When you click on the finish button you should see a new worksheet in your Excel document that resembles the one shown below in the image.
In my example I’m going to use a Slicer to help me dynamically choose when country I want to view vendor information from. So the first thing is to set your row values to Vendor Name.
Then to add the slicer select both of your row columns from your pivot table, then under the insert tab click on the slicer button. Select the option you want to slice your data by. In my example I chose the option for Country.
So now on your screen you have your slicer in place. This is what we will use for filtering our results. Now to add some more data! The next thing we want to add to the table is the Billed Life column. Click on that and you should have a screen that looks like the one below. Notice in my example I went ahead and added two more slicers in the event I wanted to slice the data based off more than just the country.
So if you click on your slicers you will notice that based on what you have selected that your data changes with it. Now another nice thing to note is that this data is live and you can refresh it at anytime by going back to your data worksheet and refreshing the query.
With our slicers now in the worksheet it is time to add a chart. To add the chart simply highlight the top to columns of the pivot table. This tells Excel what data you want to use for your chart. For my example I’m going to let Excel recommend a chart for me based on the types of data included in my pivot table. In this instance Excel recommended a column chart so that is what I chose.
When the chart is inserted into the worksheet it should look something like the image below. During the course of writing this I played around with the chart color style so your chart may look a bit different.
So now lets see this in action. I’m going to select New Zealand as my country and you can see how the data is filtered down to only that country. As you see by the image below I have drastically reduced the amount of information on my dashboard.
Now another thing to note is looking at the Vendor Class ID slicer I have two options. If I click on one of them the data is reduced down even further.
Wrapping it up
So this brings us to the conclusion of Using Power Query for Excel for Vendor Analysis. I hope this information helps get you started on using these great tools from Microsoft to help provide you with some better insights into your data.
If you are new to the world of creating dashboards and you work with Dynamics GP I would like to highly recommend you pickup a copy of Mark Polino’s book titled Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 from PACKT Publishing.