Forum Discussion

matout's avatar
matout
Helper I
4 years ago
Solved

Extracting text from string based on match from another text

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

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    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

     

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    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

     

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi matout 

     

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

     

    Best Regards,
    Community Support Team _ Jing

  • monojchakrab's avatar
    monojchakrab
    Resolver III
    3 years ago

    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