Binary.FromText - Binary Function

Binary.FromText is a Power Query M function that converts a text value to a binary value using the specified encoding (Base64 or Hex). The function returns a binary representation of the text value.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Binary.FromText(
   text as nullable text,
   optional encoding as nullable number,
) as nullable binary

Argument	Attribute	Description
text		The text to convert into a binary value.
encoding	optional	Specifies the BinaryEncoding.Type that determines the encoding type applied to binary data.. By default, if this argument is not specified, the function uses BinaryEncoding.Base64, suitable for base-64 encoding. The alternative option available is BinaryEncoding.Hex, used for hexadecimal encoding.

Description

The Binary.FromText function in Power Query converts a text value into a binary format (a list of numbers). An optional encoding parameter can be specified to indicate the encoding method used for the text value. The function supports the following encoding types BinaryEncoding.Base64 and BinaryEncoding.Hex.

Examples

Turn HEX into RGB value

One example that uses Binary.FromText is below code that transforms a HEX value into RGB:

let
    // Input hex color
    HexColor = "#26BFA3",

    // 1. Remove the leading “#”
    Clean = Text.TrimStart(HexColor, "#"),

    // 2. Parse the cleaned hex into a list of three byte values
    Bytes = Binary.ToList(Binary.FromText(Clean, BinaryEncoding.Hex)),

    // 3. Turn each byte into its decimal text form
    TextBytes = List.Transform(Bytes, each Text.From(_)),

    // 4. Join the three numbers with commas
    RGBText = Text.Combine(TextBytes, ", ")
in
    RGBText

This example uses the enumeration BinaryEncoding.Hex together with Binary.FromText to return a binary value from the original HEX value. It then continues its logic to end up with an RGB value.

Create Table from Binary Value

A useful scenario for using binary values is for encoding. For instance, when creating a table with the column ‘Column1’ and the values “a”, “b” and “c”, Power Query generates the following code:

Table.FromRows (
  Json.Document (
    Binary.Decompress (
      Binary.FromText ( "i45WSlSK1YlWSgKTyUqxsQA=", BinaryEncoding.Base64 ), 
      Compression.Deflate
    )
  ), 
  let
    _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
  in
    type table [ Column1 = _t ]
)

Notice the text i45WSlSK1YlWSgKTyUqxsQA=? It is the binary representation of the created table. Encoding a value to binary and then compressing it to a text value can improve security, reduce storage space and potentially increase the speed of data retrieval.

Basic Decoding

In its most basic form, you can turn text into binary by using the Binary.FromText function. Let’s say we try that for the text value “2203”. Here’s how you can do it:

// Output: Binary.FromText( "2203", BinaryEncoding.Base64 )
Binary.FromText( "2203" )

Decoding this value by default happens using BinaryEncoding.Base64.

In case you want to make use of hex encoding, you can make use of the enumerations BinaryEncoding.Hex.

// Output: Binary.FromText( "EBE=", BinaryEncoding.Base64 )
Binary.FromText( "1011", BinaryEncoding.Hex )

Learn more about Binary.FromText in the following articles:

Converting HEX to RGB Values in Power Query M
This article explains the conversion process of HEX values into RGB. » Read more

Other functions related to Binary.FromText are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/binary-fromtext