39: Power Query Book Author Ken Puls

My greatest excel challenge is always having to combine several data extracts from several various databases and creating one click solutions for our Executive Management Team. I have learned so many various techniques from the Industry Experts (Excel MVP’s) that I have become my companies biggest evangelist for Business Intelligence. My greatest accomplishment has some of the following features included:
– 3 Cascading Filter Controls (=index)
– 20 Sort Radio Buttons (=offset, =rank)
– Countless vlookups
– Smart Charts & visually pleasing Conditional Formatting Colors.

When all said and done, the spreadsheet displayed 1 worksheet and I had 15 supporting worksheets. We now have a solution that gives our C-Suite Team the ability to gauge performance for each individual entity.

I also had to ensure the solution worked in versions of Excel 2013, 2010, 2007 and dare I say 2003. Yep, we have a few individuals in the field who have older versions of excel that we must continue to support.

Please consider this my entry for the autographed copy of M is for (Data) Monkey book.

My most challenging endeavor in excel was the development of a computational fluid dynamic model of the air distribution within a data center and if color code if there was sufficient air being delivered to the face of server equipment. Considerations built into the model was real-time data acquisition of cooling equipment, power draws within racks, size of perforated tiles and air flow characteristics, distance from the closest piece of equipment, static pressure and many other considerations.

Result, model reflected actual distribution within 10% and actively mitigates risk due to insufficient cooling.

A company interested in being acquired sent me their sales history. I don’t know what POS they had but it was a 9,000-page PDF which was terribly formatted and not easily pasted into Excel — even if you tried putting into Word first.

My boss assumed we’d have to keypunch the whole list which would literally have taken a solid working month or more. Instead I was able to use VBA to transpose the list of data into a workable table of data. But the formatting was not consistent — missing fields, etc. — so I had to add a lot of rules and criteria into the command to get it to work. It worked and saved a month’s labor!

I am somewhat of rookie but excel changed my life when I discovered I could use it to import data into my access database if I set up an excel worksheet properly. I learned that I was able to copy large amounts of data relating to laboratory results ( date, value, test typre,ranges etc) from another program an and past into word ( I’m still not sure why it would not format correctly when I try to go straight to excel but the table pasted into word easily. I then set up a workbook with macros that allows me to import the word document table into excel in the correct format ( I did this using Google to find VBA code to import word table to excel and then modified until I got the code to import the data exactly how I wanted it , then developed macros to clean up the data and convert data into data that may access database can easily recognize. When I started the job I had to enter each specimen in by hand …and this was extremely time consuming and mind numbing and error prone ..I reduced my time spent abstracting at least by at least 75 percent and the less I have to input by hand the more accurate the data is!!! I have been hooked ever since! Wow I am really a nerd!