Binary.ToText

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

Now, while Binary.ToText can convert binary to text, its real power emerges when you pair it with other functions. This allows you to pull off some genuinely remarkable data transformations, like morphing entire 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:

1. Table.ToRows changes the table into a collection of lists. Every list represents the values from a row in the original table.
2. Json.FromValue takes these lists and turns them into a Json format.
3. Binary.Compress then compresses this Json data into binary using the Compression.Deflate method.
4. 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.

Related functions

Other functions related to Binary.ToText are:

• Binary.From
• Binary.FromList
• Binary.FromText
• Binary.ToList

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