include value from column only if it includes the value of another column

[ahhollan]

I have a table laid out as below.

Bldg ID      Name

CUF123     CUF main

CUF456    CUF storage

I have another table from another system with the same type of date only the id's are different. However, the Bldg ID is appended to the name as below.

Bldg ID      Name

123456      CUF main (CUF123)

78987        CUF storage (CUF456)

I'm trying to create a table with a row that contains all fields from both tables where the Bldg ID from the first table is in () in the Name on the second table. How do I accomplish this?

[PijushRoy · Accepted solution]

Hi @ahhollan 

In Table 2, 
Create a duplicate of the Name column in Power Query
Then use Split Column option with use as follow

Rename the column header as per your require

Let me know, it solve your requrement

If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

[ahhollan]

That worked perfectly. Thanks for your help.