Forum Discussion

james_8287B's avatar
james_8287B
Regular Visitor
8 years ago
Solved

Creating a new table from text search

I have a Keywords table:

 

Keyword
KeywordOne
KeywordTwo
KeywordThree

 

and a Messages table:

 

IdMessage
1This message contains KeywordOne
2This contains KeywordOne and KeywordTwo
3KeywordTwo and KeywordThree are in this message
4No keywords here

 

I know how to search messages for keywords and expose this though a column, such as:

 

ContainsKeyword = IF(
      SUMX(Keywords,
           FIND(
                UPPER(Keywords[Keyword]),
                UPPER(Messages[Message])
                ,,0
               )
          ) > 0,
      TRUE,
      FALSE
     )

but what I'm looking to create a new table like so:

 

MessageIdKeyword
1KeywordOne
2KeywordOne
2KeywordTwo
3KeywordTwo
3KeywordThree

 

Some things to note:

 

  • Messages are of varying length
  • Some messages contain no keywords
  • Some messages contain many keywords
  • More keywords will be added over time, ideally with no additional changes other than adding the keyword to the table

If it's possible to do this with a measure on either table, that would be even better. Any help would be most appreciated.

 

Thanks

  • OwenAuger's avatar
    OwenAuger
    8 years ago

    james_8287B

    You would really have to create a new table (rather than adding a measure to either table) because your new table expresses a many-to-many relationship between Messages and Keywords.

     

    You can create such a table using DAX, using GENERATE to join the tables:

    MessageKeyword = 
VAR JoinedTables =
    GENERATE (
        Messages,
        FILTER (
            Keywords,
            FIND ( UPPER ( Keywords[Keyword] ), UPPER ( Messages[Message] ),, 0 ) > 0
        )
    )
RETURN
    SELECTCOLUMNS (
        JoinedTables,
        "MessageId", Messages[Id],
        "Keyword", Keywords[Keyword]
    )

    I'm sure you could also do this with Power Query.

     

    Regards,

    Owen

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    james_8287B

    You would really have to create a new table (rather than adding a measure to either table) because your new table expresses a many-to-many relationship between Messages and Keywords.

     

    You can create such a table using DAX, using GENERATE to join the tables:

    MessageKeyword = 
VAR JoinedTables =
    GENERATE (
        Messages,
        FILTER (
            Keywords,
            FIND ( UPPER ( Keywords[Keyword] ), UPPER ( Messages[Message] ),, 0 ) > 0
        )
    )
RETURN
    SELECTCOLUMNS (
        JoinedTables,
        "MessageId", Messages[Id],
        "Keyword", Keywords[Keyword]
    )

    I'm sure you could also do this with Power Query.

     

    Regards,

    Owen