MS Excel Power Query — “Are You Kidding Me?” — #1
Querying a 2005 Word document on the Web
The more I work with Power Query, the more astounded I am at some of the data sets I am able to seamlessly convert to usable data.
So, I’ve decided to collect the queries that have surprised me — and saved me a ton of time! I will be adding Medium posts as I come across them. I hope it might open others eyes as to the data opportunity sitting right in Excel!
Overview
In this recent Medium post, I had the need to create a separate data table to provide an NTEE (“National Taxonomy for Exempt Entities”) description to match the NTEE Code provided in the original dataset (see “3 — Data Joining”) .
The original article’s authors created a separate Excel table that they used for their data table.
But, could I do it directly from the web? Sure can!
Process to Create NTEE Description Query Table
- Open website link and find source data reference. Copy link address.
- As mentioned above, this is a MS Word document link NOT in the cleanest format.
- In Excel (version 2016 here), go to “Data >> From Web” and paste the link copied above.
- Right-Click and select “Convert CSV”
- Table shows up in “Edit” view. This is where we shape the data into the data table we need.
- Perform steps to edit the data table and here is the final query table.
Download Query File
If you want to look at the query and related steps, you can download the Excel file here.
About Don
“It’s time for DIFFERENT”
Don is passionate about helping professionals and organizations keep up, develop and adapt to the changing business world that we operate in.
