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


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.


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.


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.

2 Responses to Converting a Binary Table To Hex With Power Query

  1. Joel Lagerquist says:

    People who write the documentation try to be precise. It can become confusing though.

    Why not just say. The register value divided by 3 is the voltage.

    Saying what the value of a bit is seems confusing. I have seen similar things on ADCs.

    I know you are using it as an example for using spreadsheet functions.

    • mathscinotes says:

      Hi Joel,

      Great to hear from you! Hopefully, the new job is going well.

      This is a real example from a product spec. I would much rather just work with formulas. However, it is what it is. I see tables like this being used all the time when a simple formula would be better. In this case, I needed to use the table in a document, so I used Power Query for converting the binary columns to hexadecimal, which I prefer for coding.



Leave a Reply

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