Forum Discussion
Help with looking up multiple values in different columns and combining results
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 |
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.
2 Replies
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.
v-chuncz-msft Awesome, thanks for the help!
