Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |