Using Power Query for Excel for Vendor Analysis–Part One

In this post I will show you how to use Power Query add-in for Microsoft Excel to create a dashboard for viewing basic information about vendors in Microsoft Dynamics GP 2013.  This example will also work for Dynamics GP 2010. 

I really do love Power Query for the ability to grab and transform data in Excel from a variety of different sources.  The one drawback I have found so far is that while I can connect to my GP database I only have the ability to browse tables.  If the information I want is going to be from a view I have to manually write a simple select * statement to get that information.  

Lets Roll!

In this example we are going to use a view provided to us called dbo.vendors which has a ton of information that we will be able to make good use of in the creation of our dashboard. The first step is to open Excel and click on the Power Query tab.

 1_power_query

As you can see this tab closely resembles the DATA tab you would normally use to pull down information into Excel. For this example we will keep it simple and select From Database > From SQL Server Database

2_fromsql 

The next window you are going to be looking at is where you will populate the name of your SQL server and what database you want to connect to.  From the image you can see that my server is SAMSUNG\SQL2014 and I want to use the database FAKEC.  I then clicked on the option for SQL Statement and populated SELECT * FROM DBO.VENDORS then click on OK.

3_sqldatasource

Once you clicked on OK Power Query will begin to import the data. The first thing you will notice is that this view has a ton of columns associated with it.  This will need to be cleaned up before we can fully load all the data.  This is also evident by the message at the bottom of this screen.

4_Import_Cleanup

So at this point we need to determine what columns we are going to keep for this dashboard.  Since I’m looking to do a simple dashboard we will keep only a few columns and delete the rest.  Technically we could have skipped this step if I would have specified the columns I wanted to use in my original SELECT statement.  To remove the columns you don’t want simply right click on it and select the remove option.  You can also use shift and select multiple columns in a row to allow for quicker cleanup.  If you have columns spaced apart ctrl + click works as well.

5_RemoveColumns

If you have a lot of columns in the ribbon is a button that says Choose Columns which provides you with a list of all the columns in the workbook you can simply uncheck the columns you did not want.

Moving On

With the columns narrowed down to just a handful, click on the Apply & Close button and the data is then brought into Excel.  The first thing you are going to notice is that on the right hand side of the screen you is a list of queries for your workbook.  In our example we only have one query and if you mouse over it you will see what is know as “The Pee” which provides a snapshot of the data that query is going to produce if loaded into the workbook.

6_ThePeek

Until We Meet Again

We are currently at a stopping point for this post.  In this post I showed you how to use Power Query to bring data into Excel and how to narrow down your column choices either by manually deleting them or using the selector tool.  I would encourage you to take some time practicing importing data from your databases and exploring all the great features Power Query has to offer.  In the second part of this blog post we will dive into creating the dashboard for some really easy analysis. 

Add a Comment

Your email address will not be published. Required fields are marked *