The M Code Behind the Power Query M function BinaryFormat.Null

What is BinaryFormat.Null?

BinaryFormat.Null is a function in Power Query that represents null values in binary format. It is used to handle null values in binary data sources, such as working with binary files or reading data from a binary database. The function returns a 4-byte null value, represented in hexadecimal format as “00000000”.

Understanding the M Code Behind BinaryFormat.Null

The M code behind BinaryFormat.Null is quite simple. The function is represented as follows:

BinaryFormat.Null = () =>


The function takes no parameters and returns a binary value of four bytes, which represent the null value. This is achieved by calling the #binary function and passing in an array of four zeros.

Using BinaryFormat.Null in Power Query

To use BinaryFormat.Null in Power Query, you can simply call the function in your query. For example, if you are reading data from a binary file and encounter a null value, you can replace it with BinaryFormat.Null as follows:


Source = Binary.Buffer(File.Contents(“C:DataBinaryFile.bin”)),

ReplacedNull = Table.ReplaceValue(Source, null, BinaryFormat.Null, Replacer.ReplaceValue,{“Column1”})



In this example, the Table.ReplaceValue function is used to replace any null values in "Column1" with BinaryFormat.Null. The result is a table with all null values replaced with the binary null value.

BinaryFormat.Null is a useful function in Power Query for handling null values in binary data sources. Its M code is simple and straightforward, making it easy to understand and use in your queries. By using BinaryFormat.Null, you can ensure that null values in binary data are handled correctly and consistently.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)