Look Up Comma Delimited Values in a Cell

[bdusen]

Hi all,

I've read dozens of other responses across these and other forums and I'm trying to figure out the best solution. I'm building reporting views off of massive data extract files that are produced nightly by one of our systems. Any field that references a user who fills a specific role uses an alphanumeric user ID instead of a name. There is a Contacts table that cross references the user ID with the name.

It's easy enough to create a relationship between tables to link the user ID with the name. Where I'm running into trouble is scenarios where there are multiple users in a role. For these instances the system extracts return the user IDs as a single comma delimited string.

I've tried splitting these into separate rows in Power Query but I'm running into issues merging the full names back to a single value using CONCATENATEX.

Here's an example of the data. We'll call this table "Reviewer"

Item ID	Reviewer
RR-12345	ABC1234
RR-23456	DCB3456
RR-78945	CCB8912
RR-36985	AAB7890
RR-74125	DCB3456
RR-98765	ABC1234,DCB3456,CCB8912
RR-25478	DCB3456
RR-36789	CCA4567
RR-23589	DDB5678,AAB7890
RR-11124	DDB5678,ABC1234
RR-22436	CBD2345

And here's the table linking the user ID with name, called Contacts: 

User ID	Name
ABC1234	Smith, John
CBD2345	Peterson, Michelle
DCB3456	Holland, Jessica
CCA4567	McEntee, Donna
DDB5678	Lopez, Mike
AAB7890	Dawson, Alan
CCB8912	Scott, Maxwell

This is what my desired output would be:

Item ID	Reviewer
RR-12345	Smith, John
RR-23456	Holland, Jessica
RR-78945	Scott, Maxwell
RR-36985	Dawson, Alan
RR-74125	Holland, Jessica
RR-98765	Smith, John ; Holland, Jessica ; Scott, Maxwell
RR-25478	Holland, Jessica
RR-36789	McEntee, Donna
RR-23589	Lopez, Mike ; Dawson, Alan
RR-11124	Lopez, Mike ; Smith, John
RR-22436	Peterson, Michelle

[nandic]

Hi @bdusen ,
Attached demo file based on your demo data.
Steps:
1) table Reviews, split user id column and then unpivot to get 2 columns: item id, user id
2) create relationship between Reviews and Contacts. Contacts will have 1 value of User Id, Reviews will have multiple
3) create additional column in dax in Reviews table: 

User Name = RELATED(Contacts[Name])
4) finally, create new table based on dax expression: 

Summarized data =

SUMMARIZE(Reviewer,Reviewer[Item ID],"Users",CONCATENATEX(VALUES(Reviewer[User Name]),Reviewer[User Name],"; "))

Yes, it is new table, but at least for start with something 🙂

Cheers,
Nemanja

[SamWiseOwl · Accepted solution]

Do you want the output to be a calculated column or a measure?

Also have you tried Fuzzy Merge in the Query editor?

Assuming you split the Reviewer onto seperate rows then joined to the userid:

Cross filter lets the Many side of the join filter the 1 side.

So for each Item show the matching users.

For the same outcome but using a calculated column without unpivoting:

Apply a filter to the contacts table and test if the current row is within the current reviewer column.

[bdusen]

The calculated column in the second part is EXACTLY what I was looking for. There might be several columns on a given table that have delimited user IDs so being able to do this on a per column basis without unpivoting is insanely helpful. THANK YOU!