List functions can become your favorite set of functions if you often need to manipulate text (aka “keywords”). You are now probably wondering what “list” function even means in Power Query.
Basically, a “list” is a special object whose elements can be easily manipulated separately.
So let’s have a taste of what can be achieved. Assume you have these 4 keywords:
- “I want to buy bananas”
- “I want to buy apples”
- “buy apples”
- “buy bananas”
Your tasks are:
- From every keyword, pull the first 2 words
- From every keyword, pull the 2nd word
- Sort all words within the keyword in alphabetical order
- From every keyword, pull all the words starting with “w”
- From every keyword, pull all the words containing “b”
- In every keyword, always capitalize only the second word
The basic steps steps are:
- Convert the keyword to a list via Text.Split function
- Use list function(s) of your choice to manipulate the list to achieve the desired transformation
- Convert the list back to text with Text.Combine function
Text.Split function converts text to list.
Example: Text.Split([Keyword],” “)
First argument is the text which needs to be manipulated, usually a column. The second argument is the delimiter which will be used for splitting the keyword into the list of words. So it needs to be a space in our case.
Updated on 2017-09-30: To be on safe side, you may want to Text.Lower() function first so all the text in [Keyword] is converted to lower case. Cases matter in e. g. in List.Sort function which will be used later. The function would then be written as Text.Split(Text.Lower([Keyword]),” “).
As usual, you are adding a custom column. What is the result?
Hmm… It’s a list. 🙂
Now, you are able to manipulate all these items in each row via list functions. The full list of list functions is here.
So let’s start with task 1:
From every keyword, pull the first 2 words
How does it work? You need to start from the middle:
- Text.Split splits the keyword by spaces.
- List.FirstN with 2 in argument selects the first 2 items from the list.
- Text.Combine joins the selected words back to “text” by using space.
From every keyword, pull the 2nd word
The tricky part here is the List.Range function. List.Range will select items starting at Xth offset. Note that counting starts at 0 offset, not at 1! That’s first argument. The second argument is used to tell how many items from the Xth offset to select. In my case, it is just 1 because I want only 1 word.
So what if you wanted to pull only the first word? The function would be:
Text.Combine(List.Range(Text.Split([Keyword],” “),0,1),” “)
Sort all words within the keyword in alphabetical order
List.Sort is useful step in deduplicating keywords with contain words in different word order 🙂
From every keyword, pull all the words starting with “w”
The important piece here is the List.Select where you need to define the condition for selecting the right words. Don’t forget to use “each” and “_” to tell Power Query that it should operate over the “just created list”.
From every keyword, pull all the words containing “b”
Yes, this will be similar as the previous example. The only piece which needs to change is the “Text.StartsWith” part to “Text.Contains”.
In every keyword, always capitalize only the second word
if List.Count(Text.Split([Keyword],” “))>=2 then
else “Keyword has less than 2 words”
Explanation: I will leave that to you 🙂
List functions are super powerful functions to manipulate text within “cells”. If you passed your “Power Query beginner” stage, definitely start learning List functions.
Some other useful List functions not mentioned in this article are:
- List.First, List.Last
- List.Contains, List.ContainsAny
- List.Count, List.IsEmpty
The sample file with all the functions applied is here.