Disclaimer: I am a true novice to Power BI and I have searched far and wide not only in this forum but others as well and have not found an answer to the issue (that I can comprehend). Also, I'mm not sure if this post even belongs in this subforum. So my apologies, if i am posting in the worng location.
Issue: I need to categorize issues in Table 1 based on the presence of a three strings - Table 2[Keyword1], Table 2[Keyword2], and Table 2[Keyowrd3]. Table1[Description] column is where the search takes place.
I am currently conducting this in excel w/ the formula below (input into the Table 1[Issue Category cells) but I am looking for a solution within Power BI.
=IFERROR(INDEX(SymptomCode,MATCH(1,(ISNUMBER(SEARCH(Keywords_issue1,C2))*ISNUMBER(SEARCH(Keywords_issue2,C2))*ISNUMBER(SEARCH(Keywords_issue3,C2))),0)),"*Issue Classification Not Found*")
Table 1
| A | B | C |
1 | Issue Number | Description | Issue Category (from Symptom Code value in Table 2) |
2 | JH-001 | Conduits installed 2” south of indicated location on drawings | Conduit – Wrong Location |
3 | JH-002 | Existing conduit damaged during demo | Conduit - Damaged |
4 | JH-003 | Embedded conduit was not placed before slab pour | Conduit – Not Installed |
5 | JH-004 | Concrete forms were prematurely removed | Issue Classification Not Found |
Table 2
Symptom Code | Keyword1 | Keyword2 | Keyword3 |
Conduit - Damaged | damage | conduit | conduit |
Conduit - Damaged | break | conduit | conduit |
Conduit - Damaged | broke | conduit | conduit |
Conduit – Not Installed | conduit | not | install |
Conduit – Not Installed | conduit | not | place |
Conduit – Wrong Location | conduit | locat | locat |
My examples of the tables are obviously hyphenated. There are currently over 1,000 entries in Table 1 and around 300 entries in Table 2 that categorize the Table 1 issues.
I have seen the phrase, text analytics cognitive services, mentioned in forums. I’m not educated on that subject, and I do not know if that service would be applicable in a case like this. We currently do not have this service available to us, so I have to proceed and use the limited resources that are available.
Any help would be greatly appreciated!
