Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Relating child line items of same parent

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 ProcessUnique IDItem NameOwnerReviewer
A1LeaseJaneAndrew
A2LeaseJaneLaura
A3LeaseJaneJane
A4InvoiceJaneAndrew
A5InvoiceJaneLaura
A6InvoiceJaneJane
B7PayrollTomAndrew
B8PayrollTomTom
C9RentJimmyJimmy

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 ProcessItem NameReviewerOther ReviewersOwner
ALeaseAndrewLauraJane
ALeaseAndrewJaneJane
ALeaseLauraAndrewJane
ALeaseLauraJaneJane
ALeaseJaneAndrew Jane
ALeaseJaneLauraJane

 

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.

  • 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

2 Replies

  • 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
    • Anonymous's avatar
      Anonymous
      Not applicable

      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.