How to get data in a table based on condition from column from different table

[SkewedCircle]

Hi, hope everyone is doing well. I'm new to Power BI and need community's help regarding a problem.

I've two tables which are unrelated as such. Table1 (IED) has a column (Attribute:name) that contains text such as B710_BCU, B710_21_1, etc. Table2 (IED_MP) has a column (TelegramPrefix) that contains several text with text of IED[Attribute:name] as prefix such as B710_BCUabc,  B710_BCUefg, B710_21_1qrs, B710_21_1mnp, etc. I wish to create a new column IED_MP[IEDname] in Table2 with only the data of table1 column IED[Attribute:name] that is contained in Table2 column IED_MP[TelegramPrefix]. The text of each row of IED_MP[TelegramPrefix] is to be checked and matched with text of all rows of  IED[Attribute:name]. If text of IED_MP[TelegramPrefix] begins with any text in IED[Attribute:name] then that text of IED[Attribute:name] is to be added in same row in new column IED_MP[IEDname]. It is to be automated, so DAX code is needed.

Table1:

IED[Attribute:name]

B710_BCU

B710_21_1

B711_BCU

Table2:

IED_MP[TelegramPrefix]	IED_MP[IEDname]- Expected output
B710_BCUAnUn_MeasTransdIn	B710_BCU
B710_BCUApplication	B710_BCU
B710_BCUVI3p1_5051OC3phA1	B710_BCU
B710_21_1PROT	B710_21_1

Excel sheet and pbix files are attached at following link for reference:

https://drive.google.com/drive/folders/1hpLf3B9rmT1fCk7fgoeA3KFrumkhKsZ8?usp=sharing

Thanks a lot in advance for much needed help.

[dharmendars007 · Accepted solution]

Hello @SkewedCircle , 

Please try the below DAX codeby creating a calculated column, this should work..

IEDname = VAR PrefixValue = IED_MP[TelegramPrefix]
RETURN
MAXX(FILTER(Table1,LEFT(PrefixValue,
LEN(Table1[Attribute:name])) = Table1[Attribute:name]),Table1[Attribute:name])

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN