• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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
October 22, 2020 by Philip Treacy

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.
Please enter a valid email address.

Download the Excel Workbook.

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

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

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 3 Ways

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

Remove Blank Rows and Columns from Tables in 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

Extracting Data from Nested 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

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

Power Query Keyboard Shortcuts to Save Time

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 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

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

Pivot an Unknown Number of Rows into 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.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:power queryIntroduction to Power Query
Next Post:Create a List of Matching Words When Searching Text in Power QueryCreate a List of Matching Words When Searching Text in Power Query

Reader Interactions

Comments

  1. Hector mdes. de los santos feliz

    March 7, 2022 at 6:03 am

    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

      March 7, 2022 at 10:17 am

      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

    October 6, 2021 at 7:12 am

    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
    • Philip Treacy

      October 6, 2021 at 8:59 am

      Hi Alan,

      You can use Text.Replace for this.

      https://docs.microsoft.com/en-us/powerquery-m/text-replace

      If you have 2 columns, Address and State, where Address contains the State you just extracted into its own column, create a new step:

      =Text.Replace([Address], [State], "") 
      

      Regards

      Phil

      Reply
      • Alan Sidman

        October 6, 2021 at 6:30 pm

        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

          October 6, 2021 at 10:59 pm

          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

            October 7, 2021 at 4:10 am

            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

            October 8, 2021 at 8:53 am

            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

  3. Alan Sidman

    October 5, 2021 at 11:50 am

    Philip, how would you change the Mcode to actually show the word found instead of showing True or False?

    Alan

    Reply
    • Philip Treacy

      October 5, 2021 at 8:50 pm

      Hi Alan,

      I wrote about that here

      https://www.myonlinetraininghub.com/create-a-list-of-matching-words-when-searching-text-in-power-query

      Regards

      Phil

      Reply
  4. Bettina Marchl

    September 28, 2021 at 5:54 am

    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

      September 29, 2021 at 11:59 am

      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

    July 18, 2021 at 8:32 pm

    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
    • Catalin Bombea

      July 18, 2021 at 10:28 pm

      Hi David,
      You are using: each (FindStrings) =>
      Get rid of the “each” to make it work.

      Reply
  6. Tharindu Palipane

    June 28, 2021 at 4:33 pm

    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
    • Philip Treacy

      June 30, 2021 at 3:08 pm

      Hi Tharindu,

      I wrote this post to do just that

      Create list of matching words from text in Power Query

      Regards

      Phil

      Reply
  7. Cedric McKeever

    April 28, 2021 at 5:22 am

    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

      May 4, 2021 at 9:20 am

      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
  8. hue

    December 20, 2020 at 7:58 pm

    Tracy,

    my table with over 64,000 rows it takes 2 hours to return the result.
    is there any way to make it faster?

    thanks
    hue

    Reply
    • Philip Treacy

      December 21, 2020 at 9:53 am

      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
  9. Sarah

    October 30, 2020 at 5:11 am

    Please provide a video walk through of this!

    Reply
    • Philip Treacy

      October 30, 2020 at 9:43 am

      Hi Sarah,

      It’s being planned.

      In the meantime check my latest post on listing words that are found in searches

      https://www.myonlinetraininghub.com/create-a-list-of-matching-words-when-searching-text-in-power-query

      Regards

      Phil

      Reply
  10. Torstein J

    October 25, 2020 at 11:16 pm

    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

      October 26, 2020 at 12:56 pm

      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
      • Torstein J

        October 26, 2020 at 7:31 pm

        Thanks, Phil! Obviously, but I didn’t catch it. Now it works as i Wanted it to!
        regards Torstein

        Reply
        • Philip Treacy

          October 27, 2020 at 9:11 am

          No worries 🙂

          Reply
  11. Donald Parish

    October 23, 2020 at 9:48 pm

    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
    • Philip Treacy

      October 26, 2020 at 12:56 pm

      Thanks Donald. Clean code is good 🙂

      Reply
  12. Jim Fitch

    October 23, 2020 at 9:02 pm

    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

      October 26, 2020 at 12:58 pm

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

      Reply
  13. Matthias

    October 23, 2020 at 12:58 am

    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
    • Catalin Bombea

      October 23, 2020 at 10:18 pm

      Hi Matthias,
      There are many ways to achieve the same result. You might also want to look at another way to do things, using regular expressions.

      Reply
  14. AlexJ Jankowski

    October 23, 2020 at 12:37 am

    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

      October 26, 2020 at 1:16 pm

      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

        October 27, 2020 at 11:34 pm

        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
        • Philip Treacy

          October 29, 2020 at 3:30 pm

          Good luck with your learning.

          Reply
  15. Glenn

    October 22, 2020 at 11:38 pm

    Excellent post, Philip!

    Reply
    • Philip Treacy

      October 23, 2020 at 2:27 pm

      Thanks Glenn 🙂

      Reply
  16. Stephen Wright

    October 22, 2020 at 6:02 pm

    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
    • Catalin Bombea

      October 23, 2020 at 10:13 pm

      Hi Stephen,
      Power Query has more surprises..
      You can use any JavaScript function, PQ has a function Web.Page, inside that page you can add your text to be analyzed and run a script to process data.
      Here is an example of using a JavaScript function in a Web.Page created only to use the JavaScript Regular Expressions pattern matching.

      Reply
    • Philip Treacy

      November 3, 2020 at 10:38 pm

      Hi Stephen,

      This code returns the matches

      https://www.myonlinetraininghub.com/create-a-list-of-matching-words-when-searching-text-in-power-query

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.