Forum Discussion

huguest's avatar
huguest
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Lookup value from another table based on keyword being in description

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 IDReport #
A2018-1 9002
A2018-29003 
A2018-39001 

 

 

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

 

Thank you,

Hugues.

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
        huguest
        Icon for Advocate II rankAdvocate II
        8 years ago

        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. 

    • huguest's avatar
      huguest
      Icon for Advocate II rankAdvocate II
      8 years ago

      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's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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