Microsoft Excel — The Simplest Way to Understand PowerQuery
Peruse The Mashable Black Friday Sales Spreadsheet!
This morning, I was reviewing Flipboard and came across this post by Mashable.com.
For anyone interested in Black Friday sales — and staying on top of what is best deals are — the spreadsheet they created is a phenomenal resource!
The spreadsheet was compiled in a Google Spreadsheet as reflected here:
This is Where PowerQuery Comes In…
This is a wonderful resource as is, but not quite as flexible or user friendly as I would like.
Using the Excel PowerQuery add-in, I import the data into Excel and quickly shape it to get me this:
The magic of PowerQuery is I have linked this data to the web data provided by Mashable. So, whenever the data is updated, this spreadsheet will be updated simply by refreshing the query!
Not Quite Done Yet!
Considering the user, I want to provide a little bit of automation to the interface to make it friendly to use.
Here’s what was provided:
- Direct link to article
- Search box to search the “Deal” field
- Use conditional formatting to easily highlight and filter content — buttons provided to make use simple
- “Print Report to PDF” button (sorry, links won’t work in this PDF version)
- Count field so I can see how many items are currently displayed (updates with filtering)
Now to Test It
What are Deals on Kindle Devices?
- Enter “Kindle” in search field
- Rows containing “Kindle” are highlighted. Click “Filter Color” button.
Interested in TV (or related) Sales?
- Enter “TV” in search field, click “Filter Color” button
Want the Excel file? It includes the Power Query and macro code for your review and use. Get it here!
About Don
“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!
