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
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.
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...
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!
User | Count |
---|---|
125 | |
75 | |
65 | |
56 | |
55 |
User | Count |
---|---|
199 | |
104 | |
88 | |
79 | |
77 |