MS Excel: 4 Creative Power Query Data Source Approaches to Consider (Part 1)
Power Query is awesome for it’s ability to extract, transform and load (the “ETL” of ETLR — “R” is reporting) data.
What I have found is that with a little bit of creative thinking regarding the information we want, and where it is located, we can create some really useful automated information feeds and Excel dashboards.
Here are 4 sources I use frequently, aside from the usual suspects (database files, etc.).
- Files Hosted on the Web (Part 1)
- Google Spreadsheet Documents
- Web Based Information (Other than Standard Data Tables)
- Data Files Hosted On Your Local Drive or Company Network
In this post, I will explore three examples of Excel* files hosted on the Internet.
*Although Excel files are demonstrated, these could be any number format of files (.csv, .txt, etc.) — Power Query can handle them!
1 — Files Hosted on the Web
#1 — FEMA Disaster Declarations History
I teach a data analytics & reporting session that is developed from declared disasters from the last 50+ years. This is a dataset provided at www.fema.gov (https://www.fema.gov/media-library/assets/documents/28318) for download. In this session, the participants work through importing and developing the data for analysis, and then develop a basic dashboard to convey the information the deem important from the content.
Rather than download the file, we connect the query to the file on the web. This way, whenever the data file is updated by FEMA, the dataset in the Excel file is updated whenever the query is refreshed. Very handy!
So, we locate the source data file:
As I mentioned, the end result is a custom dashboard that each group presents (after extensive data scrubbing, analysis prep and debate!). Here is a sample result:
#2 — Excel “resource” provided by the Ohio Society of Certified Public Accountants (OSCPA)
Annually, the OSCPA compiles their continuing education offering for the next year and provides it to their members. Usually in a PDF format, but this year it was also provided in an Excel download format as well (very nice!).
I found the Excel file, as structured, somewhat inflexible and cumbersome. However, that was easily solved by Power Query — I simply connected to the file that they provided on their website and “transformed” the data into a analysis format that was more flexible for my use.
Again, it was as easy as copying the link address and massaging the data in Excel.
A sample of one of the end result analyses is below — I can generate a condensed report based on a number of filters to quickly narrow the content I want to see.
In this case, I want just sessions being held in Cleveland, Ohio, where I am located:
#3 — Excel “data dumps” I created, hosted on a website
Like any other computer user, I have a hard time knowing (and locating) what is located on my local hard drives, especially if it has been a few years.
However, this was resolved with the use of an Excel Add-In, Power Query and a little “elbow grease” to get my resource dashboard set up.
In my case, I have 4 hard drives — 2 at work and 2 at home that I was particularly interested in understanding and being able to access content on those hard drives.
I generated 4 lists of hard drive contents (using the ASAP Utilities Excel Add-In) and posted these Excel files on my business domain server (www.invenioadvisorsllc.com).
The final dashboard list consists over 500,000 lines, updated whenever I replace the support file(s) on the Internet. It couldn’t be any easier!
Simple filtering — search for files with “HBR” (I have used short naming conventions for years for this very purpose) in the name, that have the “.pdf” extension.
Then filter by color to collapse list.
A list of 854 records remains! The flexibility I have gained should be obvious…
In Part 2 — coming soon — I will review examples of using Google spreadsheets as sources to import data into Excel with Power Query.
I hope these ideas get you thinking about your routines and how you can better leverage Power Query!
“It’s time for different”
Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.
“What Do You Do?”
I frequently get this question. My response (it’s not what you think!)? Check it out here!