{"id":412,"date":"2024-05-31T11:55:20","date_gmt":"2024-05-31T11:55:20","guid":{"rendered":"https:\/\/datadandies.nl\/?p=412"},"modified":"2024-05-31T13:59:08","modified_gmt":"2024-05-31T13:59:08","slug":"coalesce-in-power-query-m-dealing-with-text-startswith-returning-a-null","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2024\/05\/31\/coalesce-in-power-query-m-dealing-with-text-startswith-returning-a-null\/","title":{"rendered":"COALESCE in Power Query M (dealing with Text.StartsWith returning a NULL)"},"content":{"rendered":"\n

Some time ago I wanted to find out if values in certain columns started with the letter \u201ca\u201d. I used the Power Query M function Text.StartsWith() in combination with an IF statement.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

So the block of M-code looked along the lines of the one below:<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
if<\/span> Text.StartsWith<\/span>(<\/span> <\/span>[<\/span>Letters<\/span>],<\/span> \u201ca\u201d <\/span>)<\/span> <\/span>then<\/span> \u201cStarts with A\u201d <\/span>else<\/span> \u201cDoes <\/span>not<\/span> start with A\u201d<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
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:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
if<\/span> Text.StartsWith<\/span>(<\/span> <\/span>[<\/span>Letters<\/span>]<\/span>??<\/span>\u201d\u201d<\/span>,<\/span> \u201ca\u201d <\/span>)<\/span> <\/span>then<\/span> \u201cStarts with A\u201d <\/span>else<\/span> \u201cDoes <\/span>not<\/span> start with A\u201d <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now, we\u2019ve 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.<\/p>\n\n\n\n
\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
Some time ago I wanted to find out if values in certain columns started with the letter \u201ca\u201d. 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…<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":413,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[11],"class_list":["post-412","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-powerquery"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/412","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=412"}],"version-history":[{"count":2,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/412\/revisions"}],"predecessor-version":[{"id":415,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/412\/revisions\/415"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media\/413"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=412"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}