Binary.ToText - Binary Function

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

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

Syntax

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

Argument	Attribute	Description
binary		The binary value to transform to text.
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.ToText function transforms a binary value into a text value. It optionally supports a Binary Encoding Type that’s used in producing the text value.

Examples

While Binary.ToText can convert binary to text, its benefit really shows when you pair it with other functions. This allows you to pull off some remarkable data transformations, like turningentire tables into binary text.

Converting Binary Text to Table

For instance, when you’re using the ‘Enter Data’ functionality in Power Query, the UI transforms that setup into binary code. After doing that it returns you the output as a table using a formula like the following:

Table.FromRows (
  Json.Document (
    Binary.Decompress (
      Binary.FromText ( "i45WSlSK1YlWSgKTyUqxsQA=", BinaryEncoding.Base64 ), 
      Compression.Deflate
    )
  )
)

But what about going the other way around? Can you take a table and convert it into binary text? Absolutely!

Transforming Table into Binary Text

Imagine you’ve got a table named “Source”. Here’s how you’d go about turning that table into its binary representation:

Using a combination of functions, you can transform any table into binary text. Imagine you have a table with the name Source. You can transform that table in its corresponding binary code by using:

Binary.ToText (
  Binary.Compress ( 
    Json.FromValue ( Table.ToRows ( Source ) ), Compression.Deflate 
  )
)

Breaking it down:

Table.ToRows changes the table into a collection of lists. Every list represents the values from a row in the original table.
Json.FromValue takes these lists and turns them into a Json format.
Binary.Compress then compresses this Json data into binary using the Compression.Deflate method.
Lastly, Binary.ToText wraps things up, giving you a textual representation of the binary data.

Why is this important? Taking a table and converting it to binary text can be valuable in several situations. Maybe you need to compress data for storage or transport. Or perhaps you need a binary text format of your table to integrate with other systems or tools. By understanding how to perform this conversion, you open up new possibilities for data manipulation and storage.

Transform RGB into HEX

Anbother example that uses Binary.ToText is below code that transforms a RGB value into HEX:

let

  // 1) Hard-coded RGB string
  RGB = "255, 128, 64",

  // 2) Split into three text parts
  parts = Text.Split(RGB, ","),

  // 3) Trim spaces and convert to numbers
  nums = List.Transform(parts, each Number.From(Text.Trim(_))),

  // 4) Pack the numbers into a binary buffer
  bin = Binary.FromList(nums),

  // 5) Convert the binary buffer into a raw hex string
  hexRaw = Binary.ToText(bin, BinaryEncoding.Hex),

  // 6) Uppercase and prefix with “#”
  result = "#" & Text.Upper(hexRaw)

in
  result

This example uses Binary.FromList to turns the list of RGB values into a binary value. Binary.ToText then uses the BinaryEncoding.Hex enumeration to turn the result into a HEX value. The code then concatenates these values into a single RGB value.

Learn more about Binary.ToText in the following articles:

Convert RGB to HEX in Power Query M
This article shows how you can convert RGB values into HEX. » Read more

Other functions related to Binary.ToText are:

Contribute » | Contributors: Rick de Groot, Mahmoud Bani Asadi
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/binary-totext