Leveraging Free Public Data Using Excel’s Power Query (for Business Intelligence and Business Development)

This article touches on a few hot topics: free public data, business intelligence, business development, and my personal favourite… #IDidntKnowExcelCouldDoThat.  For those of you that are avid LinkedIn users, I have also posted a version of this article here.

For years I have been listening to my clients say, “wow, I didn’t know Excel could do that!”. Over the weekend I thought this phrase would make a great hashtag, and also an interesting topic for a series of articles.

The Opportunity: Data, free data, and lots of it.

Publicly available (free!!) data is published all over the internet, which is great. This data can be leveraged for business intelligence and be a powerful tool for many companies who are striving to have the right resources, in the right place, at the right time. This is also great. So what’s the catch, you ask. The difficulty with all of this publicly available data is that unfortunately, most of the time, it is not in a format or structure that is immediately useful for analysis and data mining, or just not in the format that you need it to be.

For this article I am using an example report found online from the Alberta Energy Regulator’s statistical reports webpage. The example report (the free “data”) shown on the left side of the image below states important information about all well licenses issued across Alberta for a specific date, and a new report is published daily. This is especially useful information for oilfield service companies that are looking to have people and equipment available and positioned appropriately to be able to capture upcoming opportunities (i.e. have the right resources, in the right place, at the right time). However…


The Challenge: Format & Structure

The format of this system generated report is easy for human eyes to read and interpret, but not structured in a way that can be easily compiled and summarized. (hence the big red ‘x’ and sad face icon) Here is a link to the page where you can download a copy of this report from the AER to have a look for yourself: ST1 – Well Licenses Issued. When encountering this type of problem, I have observed a lot of people resorting to manually copying and pasting text piece by piece to build a data set to conduct an “analysis”. This is a mind-numbing and time-consuming task, especially considering there is one of these reports published for every day of the year. Summarizing all of the available reports manually would take an eternity and is simply not feasible.

What if I told you there is a better way, and that it can be done in seconds?! Sounds unbelievable, right?

The Solution: Excel’s built-in power tool, Power Query

This is where Excel comes in (yes, just the basic version of Excel that is bundled with Office 365). No macros, no VBA programming, and no advanced formulas are required… just basic knowledge of the Power Query (‘Get & Transform Data’) capabilities of Excel, a few simple formulas such as IF() statements, and some finesse for creating a process to *automate* this conversion to transform the “unusable data” into the right format and structure needed for it to tell a story. (*Note that I said automate… meaning every time you download a new report, Excel can automatically include the new data in the summary reports. No need to reinvent the wheel, just click “refresh” and like magic the report is updated with all the most recent data.)

The Result: An Excel workbook with Pivot Table reports

The image above showed the ‘raw data’ report on the left, and on the right is the resulting pivot table report that Excel can generate once the data is imported and transformed into a properly structured data set. In this example I have compiled every report published from Jan 1, 2017 to Aug 9, 2018 but isolated the month of Aug 2018. To work with the range of dates a timeline slicer has been added above the pivot tables to allow the user to select the period of time they want to see summarized. Want to see the trailing 14 days? No problem. What if you want to see the totals for activity over the last year? That is no problem either. How about monthly trends over the previous 18 months? Easy, again this is no problem!

Don’t just do an “analysis”, rather, become “empowered by analytics“!

Could your organization benefit from learning how to convert public data, notices, and bulletins into insightful data sets? It is simpler than you think, and once a process is set up it can easily be administered by novice Excel users. Please send me a note on LinkedIn if you would like to find out more, or have questions regarding how you can leverage these types of analytical capabilities for your organization.

#BusinessIntelligence #BusinessDevelopment #PowerQuery #PowerBI #PowerPivot #Excel


Some bonus content for those of you who are captivated by this topic and want a glimpse of where else this type of work leads to:

The Next Frontier: Power BI

For those who want to take a deeper dive into business intelligence modeling, the transformed data this file outputs can be loaded into Power BI and with some additional efforts can be displayed on a map to visualize the data geographically. The following image shows this next step of converting free public data into insightful and interactive business intelligence reports using Microsoft’s Power BI. Because the Power BI model is interactive, it is possible to filter and drill down into details of the report. The timelines and filters allow you to display only the information you want to see. The table is interactive and allows you to expand it to show all the Fields for each company that roll up to the totals shown. By clicking on a company name, the map adjusts to show activity related to just that company. Just think of the possibilities. Better yet, make a comment and share your ideas for how this technology can be empowering.