Math Encounters Blog

Quote of the Day

May I give you a word of advice? Next time you invade Italy, do not start at the bottom.

— General Fridolin von Senger und Etterlin, WW2 German general in Italy during WW2. This was his post-war advice to a British historian. The Allied Italian Campaign was a disaster in many ways – General von Senger und Etterlin's critique of the Allied effort was probably correct.

Introduction

Figure 1: Regular Expressions Used in My Example. Drawn using Regexpr.com.

Because many corporations are focused on the Microsoft Office suite of tools, businesses often require that I use Excel/Power Query for my analysis work so that they can work with the tools I develop after I am gone. 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.  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 if Power Query could run them.

Fortunately, I saw a blog post by Imke Feldmann that showed a hack that allows the use of a regular expression in Power Query. The hack involves writing a query that contains a small HTML page that calls a Javascript regex routine. This may not be the world's most efficient piece of code, but much of my work involves cleaning up relatively small dimension tables where convenience is more important than efficiency.

My M-code example is included in this workbook and is shown below. I used a US Census table that I copied from the Wikipedia for an application example. I use this table all the time for grouping US state data by region. While it is a small table that could have been cleaned up with only Power Query's editor (i.e., no coding), using regular expressions resulted in a tad shorter query.

Reg Ex Query

The Power Query query that contains the regex is shown below.

/*
Modified version of regex routine documented by Imke Feldman at
https://www.thebiccountant.com/2018/04/25/regex-in-power-bi-and-power-query-
in-excel-with-java-script/
Input Parameters: 
x : String to be searched
y:  search regex
z:  replacement regex
*/
(x,y,z)=>
let 
   Source = Web.Page(
                     "<script>var x="&"'"&x&"'"&";var z="&"'"&z&
                     "'"&";var y=new RegExp('"&y&"','g');
                     var b=x.replace(y,z);document.write(b);</script>")
                     [Data]{0}[Children]{0}[Children]{1}[Text]{0}
in 
   Source

Wikipedia Description of US Census Regions and Divisions

This is the text I copied from the Wikipedia.

Region 1: Northeast
Division 1: New England (Connecticut, Maine, Massachusetts, New Hampshire, Rhode Island, and Vermont)
Division 2: Mid-Atlantic (New Jersey, New York, and Pennsylvania)
Region 2: Midwest (Prior to June 1984, the Midwest Region was designated as the North Central Region.)[7]
Division 3: East North Central (Illinois, Indiana, Michigan, Ohio, and Wisconsin)
Division 4: West North Central (Iowa, Kansas, Minnesota, Missouri, Nebraska, North Dakota, and South Dakota)
Region 3: South
Division 5: South Atlantic (Delaware, Florida, Georgia, Maryland, North Carolina, South Carolina, Virginia, District of Columbia, and West Virginia)
Division 6: East South Central (Alabama, Kentucky, Mississippi, and Tennessee)
Division 7: West South Central (Arkansas, Louisiana, Oklahoma, and Texas)
Region 4: West
Division 8: Mountain (Arizona, Colorado, Idaho, Montana, Nevada, New Mexico, Utah, and Wyoming)
Division 9: Pacific (Alaska, California, Hawaii, Oregon, and Washington)

Tidied Table of US State Regions and Divisions

This is a tidy version of the Wikipedia data.

Cleaned Up Table of US States By Region and Division

Quote of the Day

Courage doesn't always roar. Sometimes courage is a quiet voice at the end of the day saying, 'I will try again tomorrow.'

— Poet and artist Mary Anne Radmacher on courage.

Introduction

Figure 1: Row of Coffee Plants in Kenya.

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.

The story of Kenya's coffee growers is similar to that of other commodity farmers. Kenya's warm climate and mountain terrain are ideal for coffee production and it currently supports ~150K coffee growers (link). However, the Kenyan coffee growers have been going through a cycle of low prices – a business cycle similar to what US milk producers are going through now.

Unfortunately, there are only a few alternatives for dealing with low prices:

• Lower your cost of production
  Tough to do – most producers have already squeezed the inefficiencies out of their processes.
• Change to a more profitable crop
  Tough to do – this generally requires new equipment, learning new skills, and developing new markets.
• Find a higher margin coffee sector
  Tough to do – others are already fighting to keep these markets.
• Cut out the middlemen and sell directly to the consumer
  This is a bit like opening a Farmer's Market. My Kenyan friend is trying this approach.

In this post, I am using Power Query to clean up some data from the International Coffee Organization (ICO) for use in generating some graphics. This is a good example to show my coffee-growing friends on how to process their data.

Background

The ICO puts its data out in Excel workbooks that are not in tidy format. My cleanup role consists of:

• • removing blank rows
  • changing the table from wide-format (years in columns) to long-format (a single year column)
  • incorporate a continent column

Power Query will allow these folks to update the automatically update the data as the ICO puts out new releases.

Analysis

There is only one file: Coffee.xlsx. You can download it from here. No VBA involved, just Power Query.

Simple Graphics

I generated a copy of Excel graphs to illustrate how to use the data. Do not send me hate mail about using pie charts. I used them here so I could compare my data results with some ICO charts.

Figure 2 shows that Ethiopia is the dominant coffee producer in East Africa.

Figure 2: Horn of Africa Coffee Growing Percentages.

Figure 3 shows that South America dominates coffee production.

Figure 3: 2017 World Coffee Growing Percentages By Continent.

Conclusion

This proved to be a nice illustration of the use of Power Query to clean up some coffee data.

Post Script

I just showed the data to my coffee-growing friends. They had no idea how simple it was to clean up the data and to augment that data with information on Kenya. We then had a long discussion on things they could add to their web site. It was great to see these folks feel empowered!

Quote of the Day

Courage is not the absence of fear, but rather the assessment that something else is more important than fear.

— Franklin Delano Roosevelt

Introduction

Figure 1: Simplified Version of the DAC Table.

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 register 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.

I wanted to convert the table's binary settings to hexadecimal for ease of programming. As I looked at the table, it became apparent that converting these binary values to hexadecimal will allow me to showcase some of Power Query's list processing capabilities.

In this exercise, I will show to use Power Query to

• Convert the binary columns to a decimal value
• Convert the decimal value to hexadecimal

I like this example because it is short and easy to follow. I use this example in my Excel tutoring to illustrate the use of lists and records. My workbook containing the m-code is here. Please refer to the workbook for the details. Below, I discuss a couple of points that people sometimes find confusing.

Background

Convert Binary to Decimal

Before going forward, it is important to understand the data types associated with a Power Query table:

• Columns are lists
• Rows are records
• You address a subset of the cells in a single table row using the syntax
  table_name{row_number}[[col1], [col2], ..., [coln]]
• This subset will have a data type of record.

The following formula is used in the Excel workbook to generate a decimal number from the individual binary digits.

Convert Binary to Hex

There are a couple of ways to perform binary-to-hex conversion. The approach here uses Power Query's Number.ToText function to perform the conversion.

Result

The workbook outputs the following table.

I have used this approach for converting numerous binary tables to hex form. This approach also provides a nice illustration of how to use the list.accumulate function.

Quote of the Day

If you haven't read hundreds of books, learning from others who went before you, you are functionally illiterate – you can't coach and you can't lead.

— Jim Mattis, scholar general

Figure 1: Glass-Encased Fuse. (Wikipedia)

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. Because of the large number of products and failures, I wanted to find a table format that would contain the required information in a condensed format. Excel's TEXTJOIN function and pivot table feature solved my problem nicely. For confidentiality and presentation reasons, my example here will contain simulated failure data that is MUCH shorter than the actual data file.

Figure 2 shows an abbreviated version of my test results table that contains the RMA number, failed assembly name, and the number of days the assembly was in service. I appended a column to this table that contains the Excel function:

=TEXTJOIN(",",TRUE,IF(C17=[Assembly],[Days in Service],""))

This function creates a comma-separated values list of all the "Days in Service" values associated with the failed assemblies. I then used a pivot table to generate my final summary table. The entire process is shown in this Excel workbook, which contains a macro from the Contextures web site that turns off the filter arrows.

Figure 2: Illustration of My Table Transformation.