cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Blondyvl
Resolver I
Resolver I

Lookupvalue in different table add data to new column

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

 

 

2 REPLIES 2
v-binbinyu-msft
Community Support
Community Support

Hi @Blondyvl ,

Please try below steps:

1. below is my test steps:

Table1:

vbinbinyumsft_0-1668997057071.png

Table2:

vbinbinyumsft_1-1668997072759.png

No relationship between both table:

vbinbinyumsft_2-1668997140777.png

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 )

  

vbinbinyumsft_3-1668997227803.png

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.

 

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...

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors