Power BI Desktop - Help with text search variables

[datanau001]

Hello all,

I'm working with a report in PBI desktop which is basically used to search text.

So, for this search I use as reference an excel file from my sharepoint (D_#TAGS_SPO'[#TAG Unique]) that contains a list of 70 #TAGS. Here are some examples:

#notinthesystem
POORQLTY - #solutionmissing
#accessories
#clarification
#IMPACT
#SPO

For this search I created two variables:

Hashtag included =
var searchhashtag = FIRSTNONBLANK(FILTER(DISTINCT('D_#TAGS_SPO'[#TAG Unique]),SEARCH('D_#TAGS_SPO'[#TAG Unique],'D_ACTIVITY'[MergeSummaryComment],1,0)),1)
return if(NOT(ISBLANK(searchhashtag)),searchhashtag,"None")

Hashtag included 2 =
var searchhashtag = LASTNONBLANK(FILTER(VALUES('D_#TAGS_SPO'[#TAG Unique]),SEARCH('D_#TAGS_SPO'[#TAG Unique],'D_ACTIVITY'[MergeSummaryComment],1,0)),1)
return if(NOT(ISBLANK(searchhashtag)),searchhashtag,"None")

And then I combine the results into a new column:

Combined Hashtags =
IF('D_ACTIVITY'[Multiple Different Hashtags]=FALSE(),'D_ACTIVITY'[Hashtag included],'D_ACTIVITY'[Hashtag included] & " " & 'D_ACTIVITY'[Hashtag included 2])

With this two variables it was working fine in the beginning since I was just having 2 #TAGS. But now as the list has increased, this solution is not working fine becasue
it is just picking the first and the last #tag from that specific text cell. The other in between will no show up.
Example:
Considering that in a text cell I have the above set of #tags, I'll get as result only #notinthesystem and #SPO.

So, I would like to request your support to know how I can change this Search variables in order to show all #tags from the text cell.

Note: I already tried to work with spliting the text by delimiters but I don't think it is a good solution since there is not a specific position for these #tags.

Thank you very much for your attention.

Br//
Datanaut

[HotChilli]

Questions:

Are these measures or columns (Hashtag included 1/2, Combined Hashtags) ?

Can you provide some sample D_ACTIVITY (in text form please)?

[datanau001]

@HotChilli : thanks for replying.

These are all columns.

In this link i'm sharing a sample of D_Activity with the text cell where we see the tags

https://docs.google.com/spreadsheets/d/1XabQRcSsD1msHU1WIVg2NTekD4FaHrcp/edit?usp=sharing&ouid=11416... 

Regards

Datanaut

[HotChilli]

I had trouble understanding what you are trying to do here.

I think you want to look through a text value for any items that you have in a predefined list.

Could you try this power query column please?

List.Accumulate(#"D_#TAGS_SPO", "", (state, current) => if Text.Contains([MergeSummaryComment], current) then state & " " & current else state)

D_#TAGS_SPO would need to be a list.  You can create this from a table in Power Query via the interface.