Forum Discussion

Wresen's avatar
Wresen
Post Patron
5 years ago
Solved

String block search

Hi and thanks for reading this.

Have tried to find an answer for this but no luck.

 

Is it possible to do a textsearch that search in blocks of every 3 letter in a string.

 

Lets say i am looking for the string abc

My text is:ababcbcazabccad

In this text there is 2 abc but only one if its based on every 3 letter.(The boxes are: aba,bcb,caz,abc,cab)

 

Thanks

Need Help
  • TomMartens's avatar
    TomMartens
    5 years ago

    Hey Wresen ,

     

    first I created a table that contains the "predefined blocks", I called this table "searchstrings" and the column "searchstring", the table looks like this:

    The table is NOT related to the item-table.

    Then I created this measure:

    Measure = 
var __SearchstringsToFind =  VALUES( 'Searchstrings'[searchstring] )
var lengthOfBlock = 3
return
COUNTX(
    Sheet1
    , var afunnystring = 'Sheet1'[String]
    var lengthofstring = LEN( afunnystring )
    var noofoccurrences = 
        IF( MOD( lengthofstring , lengthOfBlock ) = 0
            , INT( DIVIDE( lengthofstring , lengthOfBlock ) )
            , INT( DIVIDE( lengthofstring , lengthOfBlock ) ) + 1
        )
    var __blocks = 
        ADDCOLUMNS(
            GENERATESERIES( 1 , noofoccurrences )
            , "block" , MID( afunnystring , ''[Value] * lengthOfBlock - 2 , lengthOfBlock )
        )
    return
    COUNTROWS(
        FILTER(
            __blocks
            , [block] in __SearchstringsToFind
        )
    )
)

    It seems the measure is returning the expected result:

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

11 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    Wresen,

     

    In Power Query, you can split the column by number of characters (repeatedly).

     

     

     

    Now you can search for the string in each column (if TextColumn.1 = "abc" or TextColumn.2 = "abc"...).

    • Wresen's avatar
      Wresen
      Post Patron
      5 years ago

      Hi DataInsights 

      Thanks so much , but the problem is that string is VERY big so i belive it will not be good to spilt it up in a colum for each string.

      Also in the long run my thougt is to be able to do a filtersearch for some of these sting of 3 letters.

      /thanks

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Wresen ,

     

    Believe this can be done using an adjustment of a code used before but the question is on the split by the values you are searching.

     

    The link is the following

     

    https://community.powerbi.com/t5/Quick-Measures-Gallery/Find-Words-on-sentence-from-another-selected-sentence/m-p/1602658#M658

     

    In your case I have made the following measure:

     

    Find Word Formula = 
// Character that split phrase into words
VAR SplitByCharacter = "abc" 

// Temporary table that splits selected phrase into words
VAR Words_table =
    --FILTER (
        ADDCOLUMNS (
            GENERATE (
                SELECTCOLUMNS (
                    ALLSELECTED ( 'Table'[Column1] ),
                    "Find_Text", 'Table'[Column1] 
                ),
                VAR TokenCount =
                    PATHLENGTH ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ) )
                RETURN
                    GENERATESERIES ( 1, TokenCount )
            ),
            "Word", PATHITEM ( SUBSTITUTE ( [Find_Text], SplitByCharacter, "|" ), [Value] )
        )
RETURN
COUNTROWS(Words_table) - 1

     

    Has you can see below waht I'm doing is picking up your text line and replacing the text you are looking by a "|" and then creating a line for each of the |

     

    In this case you would get the following table:

    This is your text but without the ABC if we count the number of rows and subtract 1 we get howe many ABC were in the text:

     

    Please see attach file.

     

     

     

    • Wresen's avatar
      Wresen
      Post Patron
      5 years ago

      Hi MFelix  and thx so much for the code.

       

      I am not sure the code will work since the code "only" looks for abc , not that its in the right place in the string (possition 1-3 or 4 -6 or7-9 and so on, that what i call in 3 blocks)

      Also the file will have over 500k rows of items that has a this long string on each row and my thougt was that the user could filter these 500k items on a few predifined 3 block strings (the 3 block strings is a defining of the object).

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Wresen ,

     

    My final result is this

     

    The overall process is to copy a table first, then separate it, find the row where abc is located, filter and retain abc, use GROUPBY to count, and finally merge the two tables.

     

    You can check the details from the attachment.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Wresen's avatar
      Wresen
      Post Patron
      5 years ago

      HI Anonymous  and thx so much for your help.

       

      I am not sure this will work since its need to be in a measure since in the end the user will be able to filter on some predefined 3 blocks to get a resualt.
      Also it need to be in 3 blocks it does its search (string possition 1-3,4-6,7-9 and so on, the sting is very long and can contain duplicats if its not done in a 3block search, otherwise its is unique)

      /Thanks

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    Hey Wresen ,

     

    this can be done using DAX inside a measure. The idea behind this approach is the following:

    1. separate the string into single characters
    2. remove unwanted characters like spaces (maybe not)
    3. create a table that contains combinations of the characters that form the sequence of characters, maybe based on a variable number, 3, 4, ...
    4. count the number of occurrences of the searchstring in the table

    You have to be aware that this can become complex and slow, as text analysis is not the domain of Power BI.

     

    Please take the time and create some sample data and provide a link to your sample data, if there is more than the "simple" example you provided.

     

    Regards,

    Tom

    • Wresen's avatar
      Wresen
      Post Patron
      5 years ago

      Hi TomMartens 

      I have added some more data , still simple but more:

      If we look for abc , the correct ones are Green , Yellow, Black and Purple.

      My thoug it that the user should be able trough a filter choose combinations and get the value of the items summed .

      Ex he chooses abc and cee . The sum would be total 5 items with these combo (Green, Yellow, Black, Purple and also now Red (cee combo)

       

      I hope you understand what i try to explain .

      /Thanks

      edit , added link:

      https://drive.google.com/file/d/1VTKJf6ZXxG99vrZBEa6lbzGedRzuyVsz/view?usp=sharing

       

       

      ItemString
      Greenabcbabcdd
      Bluecdeababcd
      Yellowbbcabcabd
      Brownbabcabcda
      Blackaabcbaabc
      Redababcacde
      Purpleacdacaabc
      • TomMartens's avatar
        TomMartens
        Super User
        5 years ago

        Hey Wresen ,

         

        first I created a table that contains the "predefined blocks", I called this table "searchstrings" and the column "searchstring", the table looks like this:

        The table is NOT related to the item-table.

        Then I created this measure:

        Measure = 
var __SearchstringsToFind =  VALUES( 'Searchstrings'[searchstring] )
var lengthOfBlock = 3
return
COUNTX(
    Sheet1
    , var afunnystring = 'Sheet1'[String]
    var lengthofstring = LEN( afunnystring )
    var noofoccurrences = 
        IF( MOD( lengthofstring , lengthOfBlock ) = 0
            , INT( DIVIDE( lengthofstring , lengthOfBlock ) )
            , INT( DIVIDE( lengthofstring , lengthOfBlock ) ) + 1
        )
    var __blocks = 
        ADDCOLUMNS(
            GENERATESERIES( 1 , noofoccurrences )
            , "block" , MID( afunnystring , ''[Value] * lengthOfBlock - 2 , lengthOfBlock )
        )
    return
    COUNTROWS(
        FILTER(
            __blocks
            , [block] in __SearchstringsToFind
        )
    )
)

        It seems the measure is returning the expected result:

        Hopefully, this provides what you are looking for.

         

        Regards,

        Tom

    • Wresen's avatar
      Wresen
      Post Patron
      5 years ago

      Hi TomMartens 

      First i would like to apoligize for a very late answer and reply ( have been on vaction)

       

      I have tried it now and it seems to work perfectly as i want , thank you so much for this.