Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
93 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |