Converting a Binary Table To Hex With Power Query

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.

 
This entry was posted in Excel. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *