# Power Query M Primer (Part 9): Types—Logical, Null, Binary

Time to finish up our exploration of primitive, single-value holding types! We’ll learn about handling true and false, explore the oxymoron of `null` (a value that represents the absence of a value) and touch on `binary`.

# Logical

Type `logical` stores Boolean values. True and False. Not very exciting, technically speaking, but very important.

```true
false
```

This type is so simple, there’s not much to say about it. Out of the box, when converting values to type `logical`, the string “false” converts to `false` and “true” converts to `true` (imagine that!). On the number front, 0 translates to `false` while any other number is turned into `true`.

```Logical.FromText("true") // true
Logical.FromText("false") // false
Logical.FromText("something else") // error - not allowed
Logical.From(0) // false
Logical.From(1) // true
Logical.From(-199) // true
```

Other values can easily be converted to `logical` using a simple comparison or if statement.

```value = "T" // converts "T" -> true and all other values -> false
if value = "T" then true else if value = "F" then false else null // converts "T" -> true, "F" -> false and everything else -> null
```

## Best Practice Tip

Sometimes, Boolean values aren’t as obvious as true/false. Value pairs like 1/0, yes/no, Y/N and is something/is not something are all, in essence, Boolean values. Don’t let the fact that they are incognito masquerade what they really are. If you see a column made up of values like these, you’re probably best off converting it to what it really is: type `logical`. Both Power Query and the environment hosting it (Microsoft Power BI, Microsoft Excel, etc.) tend to work best when a column’s data type matches the type of data stored in the column. If it’s a logical in hiding, take away the disguise and make it a real `logical`!

# Null

Type `null` is a strange one to describe. It’s a type that always holds the same value: null.

```null
```

Null represents the absence of a value (or an unknown value or an indeterminate value). If null represents a value that’s not known, is null actually a value after all? (See what I mean, it’s hard to describe.)

Thankfully, we can leave such deep ponderings to the philosophers and computer language theorists. However, there is a practical aspect to this quandary. How should operators handle null? For example, if two nulls are compared together (`null = null`), should the result be true, because identical values are being compared, or null, because those values represent unknown and comparing unknown with unknown arguably equals an unknown result?

As you can see, there are at least a couple reasonable ways an operator can handle null. Since an operator can’t support multiple behaviors simultaneously, language designers must choose between the various possible behaviors when deciding how a particular operator will work.

In M’s case, direct equality comparisons (operators `=` and `<>`) when an argument is null evaluate to true or false:

```null = null // true
null <> null // false
1 = null // false
null <> 1 // true
```

Comparing a null with `and` returns null unless the other argument is false, in which case false is returned. When `or` is used with a null, null is returned unless the other argument is true, in which case the result is true.

```null and null // null
null and true // null
null and false // false
null or null // null
null or true // true
null or false // null
```

If null is used as an argument to almost any other operator, including less than or equal to (`<=`) and greater than or equal to (`>=`), the result is null.

```1 > null // null
1 >= null // null
null < null // null
null <= null // null
10 + null // null
null - 16.3 // null
null * 25 // null
8 / null // null
"abc" & null & "def" // null
```

(The exceptions to “almost any other operator” are `is` and `meta`—advanced operators related to getting information about a value vs. working directly with value.)

## Prefer a Different Behavior?

Sometimes, how M works with null may not be what you want. The fact that M handles nulls a certain way out of the box doesn’t mean you can’t have it operate differently—just you’ll have to do extra work to achieve the desired effect.

Take the last line of the proceeding example. Let’s say you want to concatenate a couple strings and a variable such that the strings are still combined even when the variable is null. One way to achieve this is to check whether the variable holds a null. If it does, replace the null with a blank string before concatenating with it.

```let
value = null,
NullToBlank = (input) => if (input = null) then "" else input
in
"abc" & NullToBlank(value) & "def" // "abcdef"
```

Another situation where you might want different null behavior has to do with less than and greater than comparisons. In M, if a null is compared using a relational operator (`>`, &`gt;=`, `<`, `<=`), the result is null. This makes sense from the perspective that it’s not possible to know if an unknown value is greater than or less than another value because the one value is unknown. However, another valid way of handling this situation is to rank null values as less than all non-null values.

If you prefer this behavior, you can use `Comparer.Value` to do the comparison. This library function returns 0 if the compared values are equal, -1 if the first value is less than the second and 1 if the first value is greater than the second. Unlike the relational operators, with this method null is ranked as less than all non-null values.

```Value.Compare(1, 1) // 0 (equal)
Value.Compare(10, 1) // 1 (first value greater than second)
Value.Compare(10, 100) // -1 (first value less than second)
null > 1 // null
Value.Compare(null, 1) // -1
null = null // true
Value.Compare(null, null) // 0
"a" < null // null
Value.Compare("a", null) // 1
```

Before leaving alternative null handling options, there’s one more possibility we’ll consider. Out of the box, `null = null` evaluates to true. If, you’d rather `null = null` to evaluate to null, try `Value.NullableEquals`.

```null = null // true
Value.NullableEquals(null, null) // null
```

# Binary

You’ll typically see type `binary` when working with files. Usually, you’ll use a library method (or a chain of methods) to transform the binary value into something more convenient to process, like a table.

If, for some reason, you want to literally type out a binary value, doing so is easy enough. Both lists of numbers (integer or hexadecimal) and base 64 encoded text values are supported.

Below, we see the same two bytes written out using three syntaxes.

```#binary({ 0x00, 0x10 }) // list of hexadecimal literals
#binary({ 0, 16 }) // list of decimal literals
#binary("ABA=") // base 64 encoded string
```

The standard library contains a number of functions for working with binary values. As you might expect, there are methods that convert values to and from binary. You can compress and uncompress using gzip and deflate. There’s also a method that attempts to extract content type and, in some cases, encoding and potential CSV delimiter information (might be useful, say, if you want to find all text files in a folder when they don’t all have .txt extensions). There’s even a family of functions that can be used to define a custom format parser, for the odd case when you need to parse a binary value that no library function understands.

# Type `type`

That’s it! We’re through all the types that directly hold a single value except for type `type` itself. This type holds a value describing the type of a value. Delving deeper into `type` (and the related concept of Power Query’s type system) is a more advanced topic which we’ll leave alone for now.

# Next Time

While single-value types are foundational for working with data, often we want to work with values that are grouped together in some way or another—maybe in a list, maybe as record, maybe assembled into a table. M has a type for each of these groupings. Next time, we’ll start exploring them.

Until then, happy data crunching!

## 3 thoughts on “Power Query M Primer (Part 9): Types—Logical, Null, Binary”

1. Brent

Great article, Ben! The robustness of the M language still surprises me. The different handling of null in various systems and languages can really trip people up (especially if you don’t expect null at all!), but this makes it very clear.

2. Daniel Herce

Hi Ben

A little trick I discovered yesterday. I was getting a parameter (an integer) from an Excel table. In Excel, the cell may be empty and, in that case, PQ imports this value as null, which is right.

For the next calculations, I need to convert this possible null value to 0. I tried to avoid an `if xxx is null then 0 else xxx` construction and instead I used this one:

```let
a = null,
b = List.Max({a,0})
in
b
```

a may be a null or an integer, so using `List.Max` PQ converts the possible null to cero, short and easy.

Curiously, this also makes the trick.

```a = null
b = List.Max({a},0)
```

This has no sense, at least to me. When a is null, `List.Count({a})` returns 1, so this list has one element and that’s right.

According to the MS site, `List.Max` returns the optional default value only if the list is empty, which is not the case. I expected null to be returned but instead I get 0. It seems the MS description of this function is not 100% accurate.

Regards

1. Ben Gribaudo Post author

Hi Daniel,

Thanks for sharing this great tip!

I agree that documentation’s summary description for `List.Max` doesn’t capture 100% of what it offers. 🙁

Whether `null` values are factored into `List.Max`‘s comparison is controlled by its optional forth parameter. `List.Max({null}, 0, null, true)` returns `null` while both `List.Max({null}, 0, null, false)` and `List.Max({null}, 0)` (which, in effect, defaults the forth parameter to `false`) return `0`.