Creating a new table from text search

[james_8287B]

I have a Keywords table:

Keyword

KeywordOne

KeywordTwo

KeywordThree

and a Messages table:

Id	Message
1	This message contains KeywordOne
2	This contains KeywordOne and KeywordTwo
3	KeywordTwo and KeywordThree are in this message
4	No 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:

MessageId	Keyword
1	KeywordOne
2	KeywordOne
2	KeywordTwo
3	KeywordTwo
3	KeywordThree

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 · Accepted solution]

@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

[james_8287B]

Super, thanks Owen!