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.

Series Index

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.

    Reply
  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% acurate.

    Regards

    Reply
    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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *