Never Type in a Demo

October 1st 2014

Never type in a demo.

Ever since I started doing technical presentations, I've heard that advice. After all, typing is error-prone. If your demo fails because you missed a semi-colon or spelled a word incorrectly, it can be very stressful. Especially with dozens of eyes watching you as you sit there, flustered, trying to figure out what is wrong.

So, I'm not sure what happened last week at our 6th Modern Excel user group meeting (now called Data & Dashboarding in Excel - join us!), but Ben Niebuhr & myself didn't end up just typing in front of an audience. We ended up doing work in front of an audience - actual work on a spreadsheet we had never seen before. My big fear was that at the end of the meeting we'd have accomplished nothing of note and look like idiots. And, yet, thankfully, that was not the case.

The object of the meeting was to improve a spreadsheet report. The spreadsheet (generously donated by a member of the group) took data from a standardized report, manipulated the data in Excel manually and then produced an entirely different (and more useful) report to send out.

Here is an example of the report they were getting:

Here is an example of the report they were sending out:

It's a typical scenario for folks who use data in Excel. You have data in Excel that you want to report on in a different way. You don't want to wait eons for someone to build you a report that meets your exact specifications and you don't have access to the raw data yourself. So, you take the report you are getting, you delete a few columns and a few rows. You reorder things, add some extra calculated columns. Then create a chart.

Fantastic, except it takes about 15-20 minutes and requires meticulous attention to detail. And you have to do that every month. Until the end of time.

And even that's ok, if it's just you and you'll always be there. But the one month when you're on vacation and your co-worker has to figure out the 19 different steps you follow. There goes their afternoon.

Our challenge

Our challenge was to overhaul the spreadsheet and make this process easier. In front of a live audience.

Ben, thankfully, drew the short straw and ended up projecting his work to the room first (allowing me to get a headstart). Through some careful questioning, Ben discovered that the original report on which this entire spreadsheet process was based was available in a CSV format.

So the first recommendation was to start from that CSV format, rather than an Excel format. (CSV is a straightforward extract of the data whereas the Excel format unintuitively includes headings and other fluff that get in the way when manipulating data). However, we didn't have the CSV format for our meeting. Sad face.

Ben then walked through how to improve the spreadsheet. He showed how to improve the chart and make it look more eye-catching and less like a default Excel chart. I wasn't watching (and he didn't save his work so I can't share a screenshot), but there were plenty of "How did you do that again????" and "Wow!" comments from the room.

Then suddenly Ben had to leave, and I was left at the front of the room.

Power Query

Since I didn't have a CSV version of the original spreadsheet, I thought it would be a fun challenge to try using Power Query to automate the extract and manipulation of the original data.

I wasn't sure if it could be done. I knew Power Query was good, but the spreadsheet was as difficult as they come. Valuable information (like the date) was stored in the header rows, so I couldn't simply identify the detail row and delete everything else. I had certainly never tried anything this complicated in Power Query, so of course why wouldn't I try for the first time in front of an audience?

Somehow, thanks to Ben buying me some valuable time in the first hour, I was able to pull it off and create a Power Query solution:

1) First, I deleted the excess rows at the top
2) Then I renamed all the columns appropriately and deleted extra blank columns
3) Then I removed the header rows

At this point, I was stuck. I had my "dates" in different rows to my data rows.

Example of how the dates appear as different rows to my data - how do I move those dates down?

I created a custom column that said, if the first column was a date, make the new column the same date, otherwise make the new column null.

My custom column definition

Then I used the Fill Down command to fill the date to the rows below it, before finally filtering out the original date rows.

The final result in Power Query

Once I had the data in Power Pivot, I could then quickly build a Power Pivot chart and table.

The rebuilt report - powered by Power Query

Unfortunately I missed all Ben's tips on how to make my final result look snazzy.

However, at this point, I started getting questions: would it automatically refresh? Would it? Would it?

I had no idea, I had not tried it.

So, I went back to the original spreadsheet, inserted a new row of data and then refreshed my Power Pivot. The new loan was picked up and the charts updated automatically in seconds. Easy.

The beauty of Power Query.

Thanks to Kate for providing the original spreadsheet on which our entire meeting was based.

Go back...

Visitor Comments

Great write up of the night, Leonard. This was probably the most fun I have had in a presentation! I am still bummed that i wasn’t there to see the Power Solution. And I am bummed that I rushed out so fast that I forgot to save my work (classic Excel Pro move).

Most of what I did was just put one of those new tub inserts over the existing crappy bathroom; and voila! Increase professional value. Mostly I just made jokes about cutting Longbottom and Hagrid if they didn’t start producing, while trying to figure out why the pivot table wouldn’t let me group dates (spoiler: they were formatted as text. classic excel move).

We should do something like this again. I am free on the evening of October 30th…

Posted by Ben Niebuhr on 2nd Oct 2014

Post Your Own Comment

Note that comments may not appear immediately on this site.

Commenting is not available in this channel entry.