cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Englisca Frequent Visitor
Frequent Visitor

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. 

 

 

workItemIdPerform.1Perform.2Perform.3PreparerAssignments
050a6c0a-acbf-4156-9f6c-a919016da9bd209774517450nullJohn, Mike
4692d0dd-2749-4c3c-9595-a919016db06a517450nullnullJoe
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

@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.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

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

@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.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Englisca Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 330 members 3,793 guests
Please welcome our newest community members: