Search for text in Value and replace with Value from another column in another table

[NiugeS]

Hi all.  Have been trying to get some sense of this and looked up several posts but can't really work this out.  A bit new to PowerBI so any guidance on where I might find answers appreciated.  

I have two tables; one with items, user ids and user names and another items and comments.  The comments only display the UserID and not the name.

I'm looking to create a new column that has the comments with the name rather than the UserID.

Table1 Items	Table1
Item	AccountIDs	Names
1245	[~accountid:User123abc]; [~accountid:User983];  [~accountid:UserAB328]	Joe; Lucy; Sarah
1256	[~accountid:UserAB328]	Sarah
1259	[~accountid:User123abc]; [~accountid:User123]	Joe; John

Table2 Comments
ID	Date	Comment	Desired Result
1245	01/01/2020	Hey [~accountid:User123abc], hope all is fine.   [~accountid:User983] Can you help [~accountid:UserAB328]?	Hey Joe, hope all is fine.   Lucy Can you help Sarah?
1245	20/01/2020	Yeah no problem [~accountid:UserAB328]	Yeah no problem Sarah
1256	15/03/2020	[~accountid:User123abc] You got this?	Joe You got this?
1259	17/03/2020	[~accountid:User123abc] & [~accountid:User123] will take care of this.	Joe & John will take care of this.

Any guidance on where to start or where to look would be most helpful.

Thank you

[kentyler · Accepted solution]

here is a sample that does step one

it matches the names to the user id's in the first table

https://drive.google.com/file/d/1FRtRi_075qeoqPkiGYVnhjimVhzSZFGN/view?usp=sharing

I won't try to explain the steps here. If you'd like to do a session and go over them email me at ken at 8thfold dot com

[kentyler]

a short youtube to try to explain the steps i took https://youtu.be/-7UuOb7kPJQ

[kentyler]

this is as far as i'm going to get today

you can see that we manage to replace the codes by looking up the names
next step will be to coalesce the words back into a sentence

[kentyler]

OK

I was able to get a little further with another calculated column 

and this measure

Full String =

VAR row_id = Comments[ID]

VAR result = calculate(CONCATENATEX(Comments,Comments[Name_for_ID] & " "),all(Comments),Comments[ID]=row_id)

RETURN result

and then we let a table graphic work its magic to eliminate the duplicates

[kentyler]

Here's a short Youtube trying to explain what I did https://youtu.be/NwCFKaWYwAU

[NiugeS]

@kentyler  Oh my!  I wake up to all of this!  Sorry didn't get back to you yesterday when you had posted all of this, i'm in the UK.

Thank you so much - I haven't had a chance to review everything but will do so but thank you so much!

[kentyler]

🙂

Glad it made you happy. If you'd like to do an online session and explore further. I'm around all day. Email me at ken at 8thfold dot com

[NiugeS]

@kentyler hey - only just getting a chance to look at this a bit now... thanks again.

[NiugeS]

Thanks Ken - just need to set some time aside to have a look at it properly to see it all.  I'm sure I will have questions!  Thank you so much for your help!

[NiugeS]

@kentyler Thank you for the quick reply and assistance.  I've had a brief look at the steps and kinda understand.  I'll need to spend a bit more time working it out before I try and replicate some of the steps in my own report.

Regarding the 2nd table and replace the userid with the actual name, is there any suggestions of terms I should be looking for?

[kentyler]

I think you will  have to loop over the string, finding each value between []s and look it up in table 1 and then replace the string with the value you look up. Pretty sure it can be done. I will try to work out a beginning solution for you and post a new version of the power bi file.

[NiugeS]

@kentyler  Thank you for your help - i'll do some searches and see if I can find out anything at my end.  I think i'm understanding some of the steps in the first part.. although I would never had got there 🙂