Converting HEX to RGB Values in Power Query M

Say you’re working in Power BI and have a main color palette. And to complement these, you want to get a slightly lighter shade of that color. How can you do that programatically?

Unfortunately, that’s not easy using HEX codes. But if you first convert your HEX codes into RGB values, you can easily convert them to a lighter shade and then revert them back to HEX. How convenient when you can programmatically return lighter shades of your main colors to complement them in your visuals.

This post shows how to convert HEX to RGB using Power Query M in a few simple steps. You’ll learn what RGB values are, how to break down HEX codes, and how M code turns them into RGB. Let’s walk through it one step at a time.

The logic behind converting HEX values to RGB

HEX and RGB describe the same thing, a color, but in different languages. HEX is compact and used in design tools. RGB is easier to read when working with numbers. Converting between the two just means switching formats.

In this related article on converting RGB to HEX , we packed RGB values into binary. Here, we’ll do the reverse. We’ll start with HEX and unpack it into RGB numbers.

Let’s take a HEX code like #33A1FF. That breaks into three pairs:

• 33 for Red
• A1 for Green
• FF for Blue

Each pair is a hexadecimal number. So:

• 33 → 51
• A1 → 161
• FF → 255

The RGB result is RGB(51, 161, 255).

To build this in Power Query, we’ll follow these steps:

1. Start with a column of HEX values like #FF5733.
2. Strip the # to get just the hex string.
3. Convert the hex to binary using Binary.FromText(). This gives us a list of three bytes.
4. Turn each byte into a number.
5. Join the numbers with commas to get a readable RGB string like 255, 87, 51.

This gives you a direct way to turn your HEX values into RGB, no need to decode each color manually.

Next, we’ll walk through the Power Query code that puts this into action.

Converting HEX values to RGB values

So how can we convert HEX values to RGB values using Power Query M? Let’s start with a simple table of HEX codes. This table is stored in a step called Source.

Each row has one HEX color, like #FF5733. Our goal is to break this into the red, green, and blue parts. Then show them as a comma-separated text like 255, 87, 51.

We’ll use Power Query’s binary functions to handle the parsing. Here’s the transformation step:

[
    // 1. Strip the leading “#”
    clean   = Text.TrimStart([HexCode], "#"),
    // 2. Turn the 6-char hex into a list of three byte values
    bytes   = Binary.ToList(Binary.FromText(clean, BinaryEncoding.Hex)),
    // 3. Convert each byte to text
    txts    = List.Transform(bytes, Text.From),
    // 4. Join with commas
    result  = Text.Combine(txts, ", ")
][result]

Here’s what this code does:

• Text.TrimStart: removes the # symbol from the HEX value
• Binary.FromText: decodes the 6-character HEX string into three bytes
• Binary.ToList: converts each byte into a number
• Text.Combine: combines the numbers into a readable RGB string

The result is a clean table with your original HEX code and the new RGB value side by side, ready for visuals, exports, or formatting rules in Power BI.

So what does the full M code look like?

Try it yourself

You can test this yourself by copying the code below into the Advanced Editor in Power Query. This script creates a small table of HEX values and adds a column that converts each one into its RGB equivalent.

let
    // 1) Create the base table
    Source = #table(
        type table[HexCode = text],
        {
            {"#FF5733"},
            {"#33A1FF"},
            {"#A8E063"},
            {"#FF33A8"},
            {"#6B5B95"},
            {"#FFC300"},
            {"#008080"},
            {"#C70039"},
            {"#4B0082"},
            {"#2E2E2E"}
        }
    ),

    // 2) Add a column that parses each hex triplet into R, G, B
    #"Added RGB Column" = Table.AddColumn( Source, "RGB", each
            [   // 1. Strip the leading “#”
                clean   = Text.TrimStart( [HexCode], "#"),
                // 2. Turn the 6-char hex into a list of three byte values
                bytes   = Binary.ToList(Binary.FromText(clean, BinaryEncoding.Hex)),
                // 3. Convert each byte to text
                txts    = List.Transform(bytes, Text.From),
                // 4. Join with commas
                result  = Text.Combine(txts, ", ")
            ][result],
        type text
    )
in
    #"Added RGB Column"

Conclusion

You’ve now seen how to turn HEX color codes into RGB values using just a few M transformations. You learned how to split the HEX values into three, use binary functions and return an RGB value.

If you need to adjust your colors, you now at least now how to get RGB values. The next step would be to turn these RGB values into a lighter shade.