Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi people of powerbi community - i'm fairly new to the power query as a whole and i haven't been able to wrap my head around the below problem:
I have a report coming out from a system that shows a 'Process' - 'Sub-Process' - 'Owner of Process' - 'Reviewers of Process' as shown below in a dummy table.
Parent Process | Unique ID | Item Name | Owner | Reviewer |
A | 1 | Lease | Jane | Andrew |
A | 2 | Lease | Jane | Laura |
A | 3 | Lease | Jane | Jane |
A | 4 | Invoice | Jane | Andrew |
A | 5 | Invoice | Jane | Laura |
A | 6 | Invoice | Jane | Jane |
B | 7 | Payroll | Tom | Andrew |
B | 8 | Payroll | Tom | Tom |
C | 9 | Rent | Jimmy | Jimmy |
I want to be able to provide 'reviewers' with a 'Pivot table' (they dont have BI licenses) that shows them the Items they have to review & the other reviewers they have to work with (Note owners are default reviewers). Currently the pivot table i export to excel cannot display reviewer > Process > Sub-item > other reviewers because they are not related as line items (my understanding).
Is there a way to 'flip' the relation so that each reviewer has a line item with the other reviewers to make the pivot table work -- something like this:
Parent Process | Item Name | Reviewer | Other Reviewers | Owner |
A | Lease | Andrew | Laura | Jane |
A | Lease | Andrew | Jane | Jane |
A | Lease | Laura | Andrew | Jane |
A | Lease | Laura | Jane | Jane |
A | Lease | Jane | Andrew | Jane |
A | Lease | Jane | Laura | Jane |
I've tried playing around with pivots, indexing, merging, appending, custom columns but i just can see the way to of getting to my desired pivot table output using power query - but if im missing something much easier i welcome the alternative.
Thanks in advance.
Solved! Go to Solution.
I believe you can achieve this with the following steps:
I believe you can achieve this with the following steps:
Thanks! that seems to have worked. Not sure i understand the logic of self-merging the table on itself but the result is as desired. Thanks for the help.