Help with looking up multiple values in different columns and combining results

[Englisca]

I am wondering what the best way would be to lookup the IDs in the middle columns below in order to return names from a UserID table stored elsewhere. I am planning on doing a combination of IF statements nested with CONCATENATE and LOOKUPVALUEs. Just wondering if there is a better way to more directly accomplish my goal. I have included an example below of what I would like for the output to look like. 

workItemId	Perform.1	Perform.2	Perform.3	PreparerAssignments
050a6c0a-acbf-4156-9f6c-a919016da9bd	209774	517450	null	John, Mike
4692d0dd-2749-4c3c-9595-a919016db06a	517450	null	null	Joe

[v-chuncz-msft · Accepted solution]

@Englisca,

We can use Edit Queries to get the result. Show the steps as below:

1.       Choose ‘WorkItemID’ column and use ‘Unpivot Other Columns’

2.       Merge the two queries you needed

3.       Remove the columns which are not needed

4.       Group by the workitemid to sum the value of name in UserID table

5.       Change the M language from each List.Sum([UserID.Name]) to each Text.Combine([UserID.Name],",")

For more information, please refer to the pbix as attached.

[Englisca]

@v-chuncz-msft Awesome, thanks for the help!