Power Query: True Texting
6 June 2018
Welcome to our Power Query blog. This week, I look at some useful Boolean Text() functions in M.
Cleaning data often involves transforming text strings to ensure they contain consistently formatted information. There are a few Text() functions in M which are particularly useful. I start this week with some true / false functions, and I will give an example for each one.
Text.Contains
Text.Contains(string as nullable text, substring as text, optional comparer as nullable function) as nullable logical
Returns true if a text value substring was found within a text value string; otherwise, false.
This function is either true or false, so if a particular word or series of letters exists in a column, the function will be true, otherwise it will be false. This function can be useful in conditional statements where I need to decide how to populate a new column. I give a simple example below, where I want to create a column containing the country:
I choose to create a new custom column so that I have complete control over the formula that populates my column. I choose ‘Custom Column’ from the ‘Add Column’ tab (I will use this feature for all of my examples).
The formula I have used is:
= if Text.Contains([Address],"US") then "US" else if Text.Contains([Address], "UK") then "UK" else “Other”
The resulting column extracts the country to make my data more useful and easier to combine with other tables.
Text.Endswith
This is also a Boolean (true or false) function.
Text.EndsWith(string as nullable text, substring as text, optional comparer as nullable function) as nullable logical
Returns a logical value indicating whether a text value substring was found at the end of a string.
I have added a new column to my data and I want to know which client subscribed to marketing communication. Note that I can’t just look for the substring ‘subscribed’ as one of my targets changed their mind!
I add a new custom column from the ‘Add Column’ tab.
This time, the formula is:
= if Text.EndsWith([Contact], "subscribed") then "Yes" else "No"
The two clients who are currently subscribing are identified by the new column.
Text.StartsWith
This is clearly related to the previous function, but this time I am looking for a substring at the beginning of the column.
Text.StartsWith(string as nullable text, substring as text, optional comparer as nullable function) as nullable logical
Returns a logical value indicating whether a text value substring was found at the beginning of a string.
On the query below, I want a column to indicate whether the contact is the manager at their company.
I create a custom column from the ‘Add Column’ tab.
The formula I have used is:
= if Text.StartsWith([Position], "Manager") then "Yes" else "No"
I can now see which managers have been contacted.
Text.Length
This is not actually a Boolean function, since it returns a length, but it is often used within a Boolean statement, as I show in the following example.
Text.Length(text as nullable text) as nullable number
Returns the number of characters in a text value.
On the next screen, I have some (UK) phone numbers, but not all of them include the location code. The easiest way to tell that the full number has not been given is to look at the length of the number.
I create a custom column from the ‘Add Column’ tab.
The formula I have used is:
= if Text.Length([Phone]) < 11 then "No - Check Number" else "Yes"
I can now see the numbers that may need further investigation.
Next time I will look at some useful M Text() functions that can be used to edit the text in a column.
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!
