Gaining new business insights with Power Query for Excel

Mike Tolly

In the past, business power users haven’t had the means to merge their corporate data with public data about their customers. Now they can, thanks to a game-changing new add-in for Excel, Power Query Preview. Power Query Preview for Excel enables users to find and import data directly from the Internet, or other specified data sources, and then manipulate that information for unique business insights.

Data transformations start when a user sets up a series of steps that Power Query executes on each data import, leading to a data table. During the data import phase, users can manipulate and transform that data into a form usable for their analysis. These manipulations could include changing the data type, splitting data into multiple columns, un-pivoting data—even calculations. This enables a user to build a table within PowerPivot that could be related to other data sources for a comprehensive look at reporting, dashboards, or analytics.

Power Query in action

Let’s take a look at how Power Query could be used. Marketing teams might find this new add-in particularly helpful in the customer research phase before launching a campaign. Look at this Elderly Gaming Study performed by North Carolina State University, where researchers asked seniors how often they played video games, and think about the possibilities around combining data from third-party sources with customer data to answer ad-hoc queries about where marketing dollars should be spent.

Some questions we could seek to answer:

1.     In metropolitan areas, do we have a large constituency of elderly customers that are actually buying our gaming product?

2.   In the metropolitan areas where we have customers, where should we spend our advertising dollars?

In order to attempt to answer these questions, we first need to get the age demographics of the major metropolitan areas in the US. After installing the Power Query Preview and enabling it in the COM Add-ins screen, select the desired method to import the external data. In this case, we will use the “From Web” menu option to import data from an Excel file hosted online.


Next, we perform the tasks needed to manipulate the data to create the formatted dataset necessary to perform analysis, shown below.


Then, we need to create a dataset of Core Based Statistical Area (CBSA) code mappings to zip codes. This is needed to match customers and populations in different metropolitan areas.


Finally, we need to merge the two queries above to form a single table where each zip code in a metropolitan area contains the relevant demographics data from that area.


After querying the age demographic data from the current customer base into a view, we can determine what markets should be targeted by a campaign based on the number of potential customers and what levels of market penetration currently exist today.


From there we can see that only 6 of the 10 metropolitan areas where there are customers have seniors purchasing our product, and the total market size of the seniors in those areas.


After merging the two queries and aggregating the zip codes into CBSA code (customer data was summed across zip codes and population was averaged due to granularity), the information can be used to answer the initial questions above, leading to the following conclusions:

1.     Our company has very low penetration into the senior demographic in the 10 metropolitan areas where it has customers—the highest of which is Bellingham, WA.

2.   While Chicago, IL is by far the largest market of the 10 listed, we could do more targeted marketing in Spokane, WA or Salem, OR, as they are the largest markets with senior customers.

A new world of insights

Two limitations with the current version of Power Query are related to the refresh of the imported data and secured data import. The data imported by Power Query requires a manual refresh by the user on a client version of Excel with Power Query installed. Also, the Power Query can only access data that’s either anonymous or enabled via Windows authentication.

Power Query is a powerful new tool that enables business power users to create ad-hoc reports and charts merging corporate data with public data. This is a big leap forward, allowing businesses to quickly identify drivers of key metrics that may have previously been inaccessible. Check it out, and tell us what you think in the comments below.


About Mike Tolly
I am an Information Management Consultant in Chicago, who has worked in the Information Technology field since January 2011. I work with Microsoft products to create solutions to business problems.

Leave a Reply

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

You are commenting using your 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: