Extracting text from string based on match from another text

[matout]

Hello,

I am doing a data scraping project for a client, the data will come from various sources (website) with multiple naming standards.

the idea is that I want to standarise the data to be able to analyse it.

The following table contains product name and brand, as well as standard category

I want to extract the type of device from the product name, what I am trying to do is to create a standard list of devices such as Photo printer, Earbuds, Soundbar, Vaccum, Microwave... 
then scan the whole product name colume looking for a match with that list, so I can identify what this product type is.

Will this be a good approach, and how can I do it, or is there something else that is easier and more managable

thank you

[monojchakrab]

Hi @PhilipTreacy, this is a very elegant and helpful solution. Will resolve many of my issues, entailing a lot matching of text strings.

Had a question though : what is the logic behind concatening the state and the current in the code? should it not just return the current value from the list? also if the Text.contains, returns false, meaning the string from {ProductList} is not found in [ProductName] column, then should it not return null, instead of state?

Thanks in advance

[v-jingzhang]

Hi @matout 

Does @PhilipTreacy 's method solve your problem? Let us know if you have any questions. 

Best Regards,
Community Support Team _ Jing

[PhilipTreacy · Accepted solution]

Hi @matout 

Download sample PBIX file with the following code and examples

You can do what you ask by firstly setting up a list of the products you are looking for.  This is stored in a list (the result of a query)

let
    ProductList = {"Vacuum", "Microwave", "Photo Printer", "Earbuds", "Sound Bar"}
in
    ProductList

A separate query then uses that list to check the Product Name column for those words/descriptions

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY1BDoIwEEWvMmFNtNSWA4DKQkmINbAAFrUtQkCaFKobD2/FkLiYxZ/3Z15ZejkX1j7gDXkcSxqSJpSUNC5H/D6oafJqv/TSThj94k/l9inDuyMlycXNAv8+YJRSwva0+N1DVQFGCBVL75xApkWvZshaPWvITDfOyqzsqkcF26Iz6qsF5pDScODmZuUE0cBFv1aZtqN0AgPsFOx9wBskWh+CxVR/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Name" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Product", 
    
        each List.Accumulate
            (
                ProductList,

                "",
                
                (state, current) => 
                
                    if Text.Contains([Product Name], current, Comparer.OrdinalIgnoreCase) 
                    
                    then state & " " & current 
                    
                    else state
                
            ))
in
    #"Added Custom"

Giving this result

You can add as many products as you like to the ProductList and the query will search for them.

This blog post explains the workings of the code

Create a List of Matching Words When Searching Text in Power Query • My Online Training Hub

Regards

Phil