Some time ago I wanted to find out if values in certain columns started with the letter “a”. I used the Power Query M function Text.StartsWith() in combination with an IF statement.

A problem occurred: whenever Text.StartsWith() gets a null as input, it returns a null. If you feed an IF statement a null, instead of a TRUE or FALSE, IF gets really, really angry.

So the block of M-code looked along the lines of the one below:

if Text.StartsWith( [Letters], “a” ) then “Starts with A” else “Does not start with A”

In order to keep IF happy, I used something akin to a COALESCE (which is a SQL function). The double question mark in M, acts a little like COALESCE, meaning, it will return the first non-null value. Implementing this in the block of M-code above would look like this:

if Text.StartsWith( [Letters]??””, “a” ) then “Starts with A” else “Does not start with A” 

Now, we’ve added an empty string in case there is a null in the column [Letters]. IF is happy now, which means we are happy. After all, no one likes grumpy IF statements in their code.