Lookup value from another table based on keyword being in description

[huguest]

Hello, I have been trying to find a solution for this and although I found several examples that I thought were going to work, none of them did...

I have 2 tables in a report.  One of them (Table1) contains a list of Issue IDs.  The second table (Table2) contains a report number and a free text description that contains a reference to the Issue listed in Table1. 

I need to add a column to Table1 that will contain the Report # (from Table2) based on the Issue ID (from Table1) being present in the text of the Description (Table2).  What I am trying to add is what is showing in BLUE below.

Table1
Issue ID	Report #
A2018-1	9002
A2018-2	9003
A2018-3	9001

Table2
Report #	Description
9001	This is the description related to issue A2018-3…
9002	(A2018-1) This is the description related to issue 1
9003	Report for A2018-2.

Thank you,

Hugues.

[Zubair_Muhammad]

Hi @huguest

How about extracting the ID from the description in a separate column

Then you can easily lookup

Try this calculated column in Table 2

ID =
MID ( Table2[Description], FIND ( "A2018", Table2[Description],, 0 ), 7 )

[huguest]

I ended up working of of your suggestion to make it work, thank you.  I would still be interested in a solution where the ID doesn't have to be extracte dthough (for another report).

[v-yulgu-msft · Accepted solution]

Hi @huguest,

From my opinion, extracting IssueID from description text then using LOOKUPVALUE function to reference Report# might be the best choice.

Alternatively, you can join two tables then filter out unmatched records.

CrossJoin =
FILTER (
    CROSSJOIN ( Table1, Table2 ),
    NOT ( ISERROR ( FIND ( [Issue ID], [Description ] ) ) )
)

Best regards,

Yuliana Gu

[Zubair_Muhammad]

@huguest

[huguest]

Thank you for your suggestion Zubair_Muhammad, but it will not work with my "real" dataset.  My official IDs are actually randomly generated alphanumerical codes (that are randomly positioned within the text), so they cannot be easily extracted.