Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

seaching for keywords in text for use as index seach function

I have a text string containing key words that I want to index so I can filter based on these words. The below measure, however, duplicates the Category if keys are included multiple times, for instance if both cod and herring is in the same text string it gives “FishFish” rather than Fish. I would also like to have a delimiter between multiple words such as “Animal, Insect”.  Any tips?

Key's and their Category;

Categorykey
Animaldog
Animalcat
Fishcod
Fishherring
Insectwasp

 

Text with the added column and measure filter1 shown below.

Text    filter1
Dogs don't eat codAnimalFish
In cat                               Animal
Fishing for Herring and cod        FishFish
No wasp stings dogs               AnimalInsect
Raining cats and dogs but not cod     AnimalAnimalFish
Wasping the herrings in the codcat    AnimalFishFishInsect

 

filter1 = var result=
CONCATENATEX(
    'Key',
    if(
        Search(FIRSTNONBLANK('Key'[key],1),Text1[Text],,999)<> 999,
        'Key'[Category],""
    
    )
)
Return
if(
    result<>bLANK(),
    result,
    "-"
)

 

 

 

 

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Text Data is the table which contains your text.

Words table is which contains your Category and Key

 

You will need to create an id for your category in Power Query.

 

1. Group your Word Table by Category.

 

1.jpg

 

 

 

Create an  Index Column from Add Columns. Start with Index 1.

 

2.JPG

 

 

Expand the key Column only

 

3.JPG

 

 

Create a Calculated Column 

 

Found =
var a = filter(
all('Words'[key]),
search('Words'[key],TextData[Text ],1,0)<>0
)
 
RETURN
calculate(
CONCATENATEX(values('Words'[Category]),Words[Category],","),a)

 

123.JPG

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Thanks alot, this works perfectly!

harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

Text Data is the table which contains your text.

Words table is which contains your Category and Key

 

You will need to create an id for your category in Power Query.

 

1. Group your Word Table by Category.

 

1.jpg

 

 

 

Create an  Index Column from Add Columns. Start with Index 1.

 

2.JPG

 

 

Expand the key Column only

 

3.JPG

 

 

Create a Calculated Column 

 

Found =
var a = filter(
all('Words'[key]),
search('Words'[key],TextData[Text ],1,0)<>0
)
 
RETURN
calculate(
CONCATENATEX(values('Words'[Category]),Words[Category],","),a)

 

123.JPG

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

 

 

@harshnathani  great approach. But it's cheating 🙂  Any way to do that indexing in DAX?

Hi @lbendlin ,

 

 

 

1.jpg

 

DAX to create an index column

 

Id_Dax =
RANKX(ALL(Words[Category]),CONCATENATE(Words[Category],""))
 
 
Regards,
Harsh Nathani
lbendlin
Super User
Super User

The delimiter is the easy part 

 

filter1 = var result=
CONCATENATEX(
    'Key',
    if(
        Search('Key'[key],Text1[Text],,999)<> 999,
        'Key'[Category] & ","
    
    )
)
Return
if(
    result<>bLANK(),
    left(result,len(result)-1),
    "-"
)

 

 But the repetitions are more tricky.  One question - can you guarantee that the Key table is sorted alphabetically by Category?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors