My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Searching for Text Strings in Power Query

You are here: Home / Power Query / Searching for Text Strings in Power Query
Searching for Text Strings in Power Query
by

Let's say we have a column of text and we want to search it to see if each row contains a list of words.

The source data that I'll be searching through is a table in Excel named TextTable, and the list of words I'm looking for are stored in another table named WordList.

.

source data for text string search

Table containing list of words to search for


If you want to list the words your searches find, then read this post : Create a List of Matching Words When Searching Text in Power Query.

I'm loading this into Power Query with a query also called WordList, and then transforming the table into a list. By using the name WordList in other queries I can refer to the list of words that query produces.

The query to create a list of words

Query creating list of words

The list created by the query

List of words to search for

Download the Excel Workbook With All the Sample Queries in This Post

The queries in this Excel file can be copied/pasted into the Power BI Desktop Advanced Editor and will work there too.

Enter your email address below to download the workbook with the data and code from this post.

By submitting your email address you agree that we can email you our Excel newsletter.

Finding Substrings

The first thing I'm going to do is look for substrings. An example : searching for apple in the phrase four green apples will return True because apple is part of the word apples.

This query will not be looking for exact word matches, but another query later on will be doing this.

The query to look for substrings is:

query to find substrings

After loading the source data from TextTable, the query is adding a custom column named Check.

The 3rd parameter for Table.AddColumn is a function, and I'll need to use this capability.

To search for substrings, I'm using Text.Contains but this is only able to compare one string with another. It doesn't work its way through a list checking each word in that list against the text in the current row of the TextCol column.

I need to write a function that will do this and this section of code is that function

lambda function

You can write your own custom functions in Power Query but you can also write them inline in your M code. This is known as a lambda function, or an anonymous function. You don't have to name it or declare it somewhere else.

The word FindStrings is actually the parameter name for the value passed into the function. I called it FindStrings to make it clear what the function was doing, but the function would work just as well if you called the parameter x.

To explain what this function does let's work from the inside of the function out.

You have to look at the List.Transform function at the same time as the Text.Contains function to see what's happening, but first let's understand what each functions does.

list transform explanation

List.Transform takes a list, in our case the output from the WordList query, and changes, or transforms, that list according to the result of the 2nd parameter. In this case the 2nd parameter is each Text.Contains((FindStrings[TextCol]), _ )

So for each item in WordList, call Text.Contains to check if that item is in the current row of the TextCol column.

The _ is short hand for the current item and in Text.Contains((FindStrings[TextCol]), _ )) refers to the current item in WordList.

As the function works through the WordList the result from Text.Contains is used by List.Transform to modify WordList into a new list.

Text.Contains returns TRUE or FALSE indicating if the substring was found or not. To help visualize the first test look at this

Text.Contains("One yellow mango", "apple" ))

Which yields FALSE and List.Transform changes WordList to

List.Transform after first transform

NOTE: WordList itself is not changed. List.Transform works with a copy of the WordList and returns a new list as its result.

The second test is

Text.Contains("One yellow mango", "yellow" ))

Which yields TRUE and List.Transform changes WordList to

List.Transform after 2nd transform

The 3rd and 4th tests both give FALSE because neither PEAR nor RED are in the string one yellow mango.

WordList ends up as a list of TRUE or FALSE.

List after final List.Transform

which is evaluated by List.AnyTrue as TRUE

This process is carried out for each row in the TextCol column with the end result being a column of TRUE or FALSE added to the original table.

final table of true and false values

In row 1 the result is TRUE because the word yellow is in row 1 of TextCol. Row 5 is also TRUE because apple is in the string in row 5 of TextCol.

The results for rows 2, 3 and 4 are FALSE. In rows 3 and 4 we get FALSE because the search is case sensitive. The word PEAR in WordList is not the same as pear.

Finds Substrings - Ignoring Case

To do a case insensitive search for substrings we can use the optional 3rd parameter for Text.Contains.

This 3rd parameter is a comparer which tells Text.Contains how do do the comparison.

By specifying Comparer.OrdinalIgnoreCase as the comparer, the text comparisons become case insensitive.

Case insensitive substring search

Rows 3 and 4 now give a True result too.

results table for case insensitive search

Exact Match String Searches

Let's say that we want to search only for exact words, so searching for apple in apples would return false.

To do this we need to use a different function, List.ContainsAny. This takes two lists as inputs and checks if any of the values in one list are in the other list.

Here, the list to search is the text in each row of TextCol, and the words we're looking for are in WordList.

The query looks like this

Power query for exact match string search

For each row in TextCol, List.ContainsAny compares the string of text in TextCol with WordList. Because List.ContainsAny takes lists as inputs, we have to use Text.Split to split the string in the TextCol column into a list of separate words.

The results show that only the first row gives a TRUE result because yellow is the only exact match. This search is case sensitive.

results table of exact match string search

Exact Match String Searches - Ignoring Case

The exact match search can also be made case insensitive by utilising the 3rd optional parameter for List.ContainsAny.

In the official documentation this 3rd parameter is cryptically decribed as equationCriteria. In practice you can actually write your own function for this parameter and specify how you want the comparison performed.

In the following image I've laid out the lines of the query so it is easier to see what is going on.

power query for exact match string search case insensitive

We already know what the first two parameters are so I'll explain what the 3rd one is doing. It's another lambda function taking two parameters x and y. In this situation x is Text.Split( [TextCol] , " ") and y is WordList.

The function uses the Comparer.Equals function to tell List.ContainsAny that when it does the comparison of the values x and y, ignore case.

The results look like this because the words yellow, pear and red are exact matches when case is ignored.

results table for exact match string search case insensitive

I hope you find these queries useful.

Searching for Text Strings in Power Query

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Power Query Posts

get started with power query
10x Productivity with Excel Power Query in 3 easy steps. Get data > Transform Data > Load Data = HOURS Saved!
How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables
Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query
Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query
Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query
Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts
Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query
Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips
Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns
How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.


Category:

Reader Interactions

Comments

  1. Hector mdes. de los santos feliz

    Hello friends.I saw that power query, is a poweful tool to work in excel, however if i have a reading file without any table to transform, how can power query transform it? lets suppose i have a little paragraph with somes information and somes numbers., in that case, how can i transform it? can i create a table with the paragraph with power query in excel 2013 or other? my name is Hector mdes. de los santos feliz from Dominican Republic. thank you and God bless you

    Reply

    • Philip Treacy

      Hi Hector,

      Yes you can create a table from a paragraph of text/numbers. But to properly answer you I’d need to see the paragraph(s) and know exactly what transformations you want to do.

      You can start a topic on our forum and attach your file(s) there with an explanation of what you are trying to achieve.

      Regards

      Phil

      Reply

  2. Alan Sidman

    Thanks for the prompt reply. Works great. I was using it to find the state in a full address. I’m wondering if there is a means to remove it from the address field when it is placed into the new state field.

    Alan

    Reply

      • Alan Sidman

        Custom1 = Text.Replace([Address],[States],””) generates an error message
        Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an ‘each’ expression?

        I looked at the link you provided MS and this puzzles me as the line of Mcode seems to follow exactly except that MS used actual strings instead of fields.

        Your thoughts. I think I’ll post this to your help site with the file for analysis.

        Reply

        • Philip Treacy

          Hi Alan,

          That piece of code works fine for me but it is dependent on how the data is structured. So yes, please post your file on our forum so I can have a look.

          Regards

          Phil

          Reply

          • Alan Sidman

            Posted and Val suggested a workable solution. Really surprised that your suggestion did not work for me as it was exactly the same as MS had suggested in the link you provided.

            Alan

          • Philip Treacy

            Hi Alan,

            In my comment I did say add a new step so I’m, not sure how you did this, by using the Adv Editor or the GUI interface. Looks like the code you entered is missing some crucial bits but it seems that the guys on the forum have sorted it out for you.

            Regards

            Phil

  4. Bettina Marchl

    Hi Philip,
    this is a great post and I am trying to adapt it for my task at hand (but I am still fairly new to power query). I have come across 2 challenges and I would like to get your feedback on these

    1. I am trying to use it on a dataset of 550k rows – but so far I cannot get the data to load when I close power query. Would this size be too much for this solution?

    2. Is there a way to run the query not only on one column but on multiple columns at the same time (if any textstring defined in Wordlist appears in Column A, B, C or D, then return true) ? My solution so far is to do it column by column and then add a conditional column to get a final result. But I am sure there is a more elegant way…..

    Thanks
    Bettina

    Reply

    • Philip Treacy

      Thanks Bettina.

      550K rows should be fine, you may need to use buffer functions to speed things up. Hard to say without seeing your data and code. Can you post your data on our forum?

      You can run a query against multiple columns, for example combining 2 calls to 2 different columns like this

      List.ContainsAny( Text.Split( [TextCol] , " "), WordList, (x, y) => Comparer.Equals(Comparer.OrdinalIgnoreCase, x, y) )

and 

List.ContainsAny( Text.Split( [TextCol2] , " "), WordList, (x, y) => Comparer.Equals(Comparer.OrdinalIgnoreCase, x, y) )

      Regards

      Phil

      Reply

  5. David Nightingale

    Hi – got a problem… instead of getting a column with true or false, I just get one that has a “Function” in it.

    My Code:

    let
    Source = tbl_MaterialImports_Refined1,
    #”Renamed Columns” = Table.RenameColumns(Source,{{“Description”, “TextCol”}}),
    #”Added Custom” = Table.AddColumn(#”Renamed Columns”, “Check”, each (FindStrings) =>

    List.AnyTrue(List.Transform(WordList, each Text.Contains((FindStrings[TextCol]),_))))
    in
    #”Added Custom”

    Reply

  6. Tharindu Palipane

    Hi Philip,

    Thank you for the awesome explanation. needing some direction. So I’m trying to extract rather than the “TRUE” value, the words that actually made the value true in the concatinated manner. For e.g. “One yellow mango” will have “yellow” in the column in front. If the word was “One yellow apple” it would be “yellow;apple”.

    Any idea how this can be done. appreciate a little direction to this.

    Thanks
    Tharindu

    Reply

  7. Cedric McKeever

    This is probably a stupid question.

    I do not understand what “FindStrings” does. You define it as “the parameter name for the value passed into the function. What is the value that is passed into the function by “FindStrings?” What does it do?
    Also, just above “Finds Substrings – Ignoring Case,’ you say “In row 1 the result is TRUE because the word yellow is in row 1 of TexCol. Row 4 is also TRUE …” Should that be “Row 5 is also true because apple is in the string in row 5 of TextCol.”

    Reply

    • Philip Treacy

      Hi Cedric,

      FindStrings is a lambda function, it’s declared inline in the code rather than separately elsewhere. The value passed into it is the current (row) value in the TextCol column.

      Yes it should say Row 5, thanks for that, I’ve changed it.

      regards

      Phil

      Reply

    • Philip Treacy

      Hi Hue,

      Possibly if you used List.Buffer. I’d need to see your code and data though to test it.

      Please start a topic on the forum and attach your file.

      Regards

      Phil

      Reply

  10. Torstein J

    Hi Phil!
    I really enjoyed your lesson about searching for strings using Power query.
    I have been using Power Query for a while, but I realize that there’s a lot I don’t know.
    When using PQ I have been able to transfer the results easily from PQ to the worksheet.

    With your quieries I was not able to figure out how to get the results back to the data sheet.

    I would like to have the true/false list in column B to be able to for example filter table based on the results in that column.
    I hope I have explained my problem understandable, and that you can tell me how to achieve this.

    Reply

    • Philip Treacy

      Thanks Torstein. All the queries in the workbook are set to Connection only. To load the results into a worksheet, right click on the query in the Queries and Connections list, then click on Load To … then choose Table and either New Worksheet or Existing Worksheet.

      Regards

      Phil

      Reply

  11. Donald Parish

    As a former Ruby and Clojure programmer, I’m impressed by your use of functional programming concepts applied to Power Query. I love how there is a function for what you need in m, so you can write such clean code.

    Reply

  12. Jim Fitch

    Hi Phil,
    Thanks for the detailed, inside-out explanation of how the M code functions work inside the formula. That is very helpful instruction. Thanks, too, for defining “lambda function”. I look forward to impressing my children with it; two of them are software developers. We’ll be together for Thanksgiving (not too far off); it should be a good topic for official time-outs during the football games. Ha!

    Reply

    • Philip Treacy

      Thanks Jim. Our eldest son is just getting into programming and really enjoying it. It makes for fun conversations 🙂

      Reply

  13. Matthias

    Hi Phil,

    thanks, that was very well explained.

    As you could have e.g. apple(s) and pear(s) in one sentence, you might want to know how many matches you have per sentence, instead of just match TRUE or FALSE:
    List.Count(Splitter.SplitTextByAnyDelimiter(WordList)([TextCol]))-1

    L.E.:
    Exact Match – Ignore Case is quite complex. You explained it well, but what is the reason to use the function with Comparer.Equals instead of simply using Comparer.OrdinalIgnoreCase. They render the same results for your example data.

    Regards,
    Matthias

    Reply

  14. AlexJ Jankowski

    Phi, Minda,
    These are very useful indeed.
    I am trying to build a function to provide the same functionality, but I am struggling with the syntax. Could you please possible post the correct syntax for a function that would be invoked in a new column function for one of the above cases?

    Thanks in advance

    Reply

    • Philip Treacy

      Hi Alex,

      In this file I’ve modified the Find Substrings query into a function and a separate query that calls this function. You’ll find these in the fx group when you open the Query Editor. Hope that gives you some pointers.

      I’ve also written about Power Query Custom Functions.

      Regards

      Phil

      Reply

      • Alex Jankowski

        Thanks, Phil. I appreciate your reply.
        In fact, my struggles with functions are not in concept or implementation of basic functions. (I had already read your post on functions). It is more in the syntax of more complex M statements which, embedded in functions, get even more complex. I’ll e workin on it!
        Thanks again,
        Alex

        Reply

  16. Stephen Wright

    Thank, that’s really interesting, but how could you return the item that was matched instead of just knowing that there was a match?

    Reply

Leave a Reply

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

Current ye@r *