MS Excel: 4 Creative Power Query Data Source Approaches to Consider (Part 1)

Once You Start, The Application Ideas Flow

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:

Image for post
Image for post
https://www.fema.gov/media-library/assets/documents/28318
Image for post
Image for post
FEMA file — “Copy link address” rather than downloading 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:

Image for post
Image for post
Sample FEMA dataset analysis dashboard report

#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.

Image for post
Image for post
OSCPA website — Excel download connection
Image for post
Image for post
OSCPA website — don’t download, copy link address and use Power Query

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:

Image for post
Image for post
CPE content list — all courses displayed
Image for post
Image for post
OSCPA CPE Content list — slicer button click to filter on just Cleveland courses

#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!

Image for post
Image for post
Data list table in Excel — 545,000 records

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…

Image for post
Image for post
Filtered list of files — 545,000 to 854 with one simple search request
Image for post
Image for post
Every line includes a hyperlink to the file location — accessible with one click (as long as the drive containing the file is online).

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!

About Don

Image for post
Image for post

“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!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store