Lookupvalue in different table add data to new column

[Blondyvl]

I am trying to create something similar to a Vlookup /transpose in excel and I can’t get it to work in Powerbi. please can anyone help me.

I am trying to lookup data in table 2 to go into table 1, if the cell containing text words, e.g., one column if the cell in table 2 contains the word 'printer','phone','keyboard', then add "printer". to the printer column, keyboard to a new column called printer keyboard in a column called keyboard and leave blank cell if no data is in the table 2 for the unique identifier.

Table 2

123344tk, help phone,

123344Ma, help printer

123456tk, help keyboard

111222ma, help printer

111222Ts help Printer

111222Ka, blank cell,

12344tk help printer

Table 1

123344tk,.......

123344Ma, ........

123456tk, ......

111222ma, ...........

111222Ts, ..............

111222Ka, ..........

12344tk, .................

So the results im looking for in table 1 is, 

123344tk, printer, phone 

123344Ma, printer

123456tk, keyboard

123344tk, phone,

123456tk, keyboard

111222ma, printer

111222Ts, printer

111222Ka

Many thanks

[v-binbinyu-msft]

Hi @Blondyvl ,

Please try below steps:

1. below is my test steps:

Table1:

Table2:

No relationship between both table:

2. add a new column with below dax formual in Table1

Column =
VAR _a =
    LOOKUPVALUE ( Table2[Column2], Table2[Column1], Table1[Column1] )
VAR len_a =
    LEN ( _a )
RETURN
    MID ( _a, 6, len_a )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Blondyvl]

123344tk, printer, phone 

A lot of the instances have multiple items, I  am trying to transpose the rows into seperate collumns, eg 12244TK has printer and phone. I have seperate collums for each item, printer, phone, scannner etc...