Category Archives: Excel

Combined Excel Timeline/Column Chart using WW2 Naval Air Data

I recently needed to generate a graph in Excel that combined a column chart with a timeline. The graph turned out well and I decided to share my work here. As my original work is proprietary, I will share the technique here using some US Navy (USN) air operations data from WW2. Continue reading

Posted in Excel, History Through Spreadsheets, Military History | Comments Off on Combined Excel Timeline/Column Chart using WW2 Naval Air Data

Web Scraping Sailboat Reviews Using Power Query

While it is true that I worked on US Navy contracts for twelve years and spent some time on ships testing new underwater vehicles, I know very little about recreational boating. However, I have always been fascinated by sailing, though this fascination has been limited to reading books about the Age of Sail. Continue reading

Posted in Excel, Personal | Comments Off on Web Scraping Sailboat Reviews Using Power Query

Visualizing House of Representative Data Using Power Query

I have been following certain bills through the US House of Representatives and wanted to know how the voting varied by US regions and political parties. I also want to generate tables that show how my state's representatives vote (Figure 1). Fortunately, the votes are documented online and Power Query was able to easily grab and process the data. Continue reading

Posted in Civics Through Spreadsheets, Excel | Comments Off on Visualizing House of Representative Data Using Power Query

Power Query Regular Expression Hack

I use Python, R, and Excel every day in the course of my work. Because many corporations are focused on the Microsoft Office suite of tools, many businesses require that I use Excel/Power Query so that they can work with the tools I develop after I am done. Fortunately, I really enjoy using Power Query, but I find it irritating that it does not support regular expressions. I must admit that Power Query's standard functions can do a good job of extracting strings, but the process is a bit tedious.  However, I have a large library of regular expressions for extracting email addresses, phone numbers, social security numbers, and the like that would be efficient for me to use. Continue reading

Posted in Excel | 3 Comments

Cleaning Up Coffee Production Data Using Power Query

I recently volunteered to do some pro-bono data analysis and front-end web development for a very nice Kenyan woman who is trying to provide a US distribution channel for some coffee growers in her native Kenya. This post documents my work on tidying her coffee data. Continue reading

Posted in Excel | 1 Comment

Converting a Binary Table To Hex With Power Query

I was recently given a specification for a Digital-to-Analog Converter (DAC) that contains a table of register settings that correspond to the DAC's output voltage. Each row of the table corresponds to one register setting and each column corresponds to a register bit. Thus, each table cell corresponds to a single bit – a 1 or 0. A simplified version of this table is shown in Figure 1 – a simplified version of the much larger table I was working with. Continue reading

Posted in Excel | 2 Comments

Another Excel TEXTJOIN Example

I have been analyzing seemingly random fuse failures in different products. The failing fuse is similar to the unit shown in Figure 1. My analysis has shown that the fuses are failing because of damage they are sustaining during the manufacturing process. In my analysis report, I was asked to list each product that experienced a fuse failure and to list how many days each product was in service before the fuse failure occurred. Continue reading

Posted in Excel | 1 Comment

Using Excel to Convert a Number List to Dashed String of Ranges

While working on a test report for an aircraft manufacturer this week, I needed to convert a large number of number lists to strings of dashed ranges. For example, suppose you are given a list {1, 2, 3, 5, 6, 7, 8, 10}. Converting this list to a dashed set of ranges means generating the string "1-3, 5, 6-8, 10." Figure 1 shows another example with an optional prefix added to each number. Continue reading

Posted in Excel | 1 Comment

Let's Grow Some Oats

I am going to grow and process some oats this year. This is a project that I have been interested in doing for a while because one of my sons is now in the oat business and he has shown some interest in working through the entire oat processing cycle. As a boy, I used to mill oats on the family farm, but I remember very little of that time. Continue reading

Posted in Excel, General Mathematics | Comments Off on Let's Grow Some Oats

Age of Supreme Court Justices at Confirmation with Power Query

I was listening to a political pundit mention that both US political parties want to confirm young Supreme Court justices to ensure that their judicial philosophies endure. I was curious as to whether that was true over time. I went to the Wikipedia and saw that they had a list of all the justices since the founding of the US and web pages for each justice. Sounds like a perfect opportunity for a bit of web scraping! Continue reading

Posted in Civics Through Spreadsheets, Excel, History Through Spreadsheets | 2 Comments