Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have a description column that contains descriptions of items. These descriptions are in a sentence format and contain one of over 30 key words. A few of the key words are below. I would like to extract the key words from the description. For Example in the table below the Description for row 1 would only show MATCode and exclude the rest of the sentence. How can I accomplish this? I am open to doing it as a measure or in the query editor. whichever is most effective because I have over 30 key words
Key Words
MATCode | ||||||||||||||||||||
LNCMCA | ||||||||||||||||||||
LMTDWN | ||||||||||||||||||||
DECWEEM@AEEG | ||||||||||||||||||||
DECEESM@HDDC | ||||||||||||||||||||
DEFFMM@LKKDDI | ||||||||||||||||||||
DSFDFMM@SSSS | ||||||||||||||||||||
NfdIS@CffffVID-19@AffS37F | ||||||||||||||||||||
NgfgDIS@gdgdVID-19@A01S37F
|
Solved! Go to Solution.
@dw700d This is a variation on Text to Table. PBIX is attached below sig.
Keyword =
VAR __Separator = " "
VAR __SearchText = MAX('Table'[Description])
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__Word", [__Word]
)
VAR __Keywords = INTERSECT(__Table,SELECTCOLUMNS('Keywords',"__Word",[Key Words]))
RETURN
CONCATENATEX(__Keywords,[__Word],",")
@dw700d This is a variation on Text to Table. PBIX is attached below sig.
Keyword =
VAR __Separator = " "
VAR __SearchText = MAX('Table'[Description])
VAR __Len = LEN(__SearchText)
VAR __Count = __Len - LEN(SUBSTITUTE(__SearchText,__Separator,"")) + 1
VAR __Table =
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Word",
VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|",IF([Value]=1,1,[Value]-1))
VAR __Start =
SWITCH(TRUE(),
__Count = 1,1,
[Value] = 1,1,
FIND("|",__Text)+1
)
VAR __End =
SWITCH(TRUE(),
__Count = 1,__Len,
[Value] = 1,FIND("|",__Text) - 1,
[Value] = __Count,__Len,
FIND(__Separator,__Text,__Start)-1
)
VAR __Word = MID(__Text,__Start,__End - __Start + 1)
RETURN __Word
),
"__Word", [__Word]
)
VAR __Keywords = INTERSECT(__Table,SELECTCOLUMNS('Keywords',"__Word",[Key Words]))
RETURN
CONCATENATEX(__Keywords,[__Word],",")
@Greg_Deckler Thank you for this solution. I've managed to adjust it to my table but now I get an expression error on line 32 not a valid table expression.
I'm sure I forgot a step just no idea what.
I tried opening your keywords.pbix but unfortunately we using an older version and I don't have permission to upgrade.
@LeVa Hard to say, can you post your code?
@Greg_Deckler I'm sorry I added a screenshot with the code, must have done something wrong there.
This is what I have now.
Thanks @Greg_Deckler for the prompt response I am not familiar with the text to table concept. What does this measure allow me to do?
@dw700d It extracts the keywords from the sentences and lists them. You can see it in action here:
Keywords - Microsoft Power BI Community
Pretty sure it does exactly what you want to be done. Basically the way it works, it converts the sentence into a table of words. It then INTERSECTS the table of words with the table of keywords. It then concatenates the results.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
81 | |
75 | |
60 | |
37 | |
33 |
User | Count |
---|---|
102 | |
56 | |
52 | |
46 | |
40 |