Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |