Relating child line items of same parent

[Anonymous]

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.

[stvsurf · Accepted solution]

I believe you can achieve this with the following steps:

1. merge the query with itself on the combination of Parent process and Item name
2. expand "reviewer" of the result table
3. hide the line where the original reviewer is the same as the new reviewer
4. 

[Anonymous]

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.