Forum Discussion
Rearranging columns with hierarchy
Hello,
I need to re-arrange two columns into three depending on hierarchy and I am not sure how to approach it.
I have three levels in the hierarchy:
A - Grand parent
B - Parent
C - Child
These three levels are distributed in two columns like so:
| Child | Parent |
| C | B |
| B | A |
| A | - |
I would like to have them in the following arrangement:
| Grandparent | Parent | Child |
| A | B | C |
Thank you in advance.
EdgarEngieImpact Have a look at the attached file. It contains a Power Query solution that uses a wonderful script that I picked-up from the Thebiccountant.com (link is in the file). I tweaked it a bit so that it matches the outcome you desire, taking no credit for it.
It's actually meant to explode a Bill of Materials with quantities into a list of all the individual items used. But it works the same for your list of campaigns. Kindly refer to the link in the file to learn more about the techniques used.
3 Replies
It's hard to follow exactly what you want here. The basic concept--of hierarchy, three level hierarchy--is clear. What's not clear is how to take that A-B-C and the labels organized in cells, what the actual data is that you're working with, how it's laid out, how it's current identified as Grandparent, Parent or Child.
I think it would help if you gave a few more illustrations complete illustrations, preferably NOT using A B C as your data elements, since they're obviously sequential in and of themselves. What are some random data elements, how would one know which is grandparent, parent or child, etc.
I see, thanks for your comments. I am trying to organize marketing campaigns hierarchally. The first table is given to me by a SalesForce report, which can provide only a two level hierarchy (Campaign name and Campaign Parent name).
How to know if it's grandparent: It doesn't have a parent associated
How to know if it's parent: it has a parent associated which in turn doesn't have a Campaign parent associated.
How to know if it's a child: it has a Campaign parent associated, which has another Campaign parent associated, which doesn't have one itself.
As a more defined example:
Campaign name Campaign parent name Campaign Grandparent 1 - Campaign Parent 1 Campaign Grandparent Campaign child 1.1 Campaign parent Campaign child 1.2 Campaign Parent Campaign Grandparent 2 - Campaign Parent 2 Campaign Grandparent 2 The goal is to have it organized in hierarchy. I tried with pivot tables while keeping only only these two columns, but the fact that the campaign hierarchy depth varies between 1, 2 or 3 levels makes it very hard, that's why I thought splitting it in three columns should make it easier. Like so:
Grandparent Parent Child Campaign Grandparent 1 Campaign Parent 1 Campaign Child 1.1 Campaign Grandparent 1 Campaign Parent 1 Campaign Child 1.2 Campaign Grandparent 2 Campaign Parent 2 - Hope it's more clear. Thank you.
EdgarEngieImpact Have a look at the attached file. It contains a Power Query solution that uses a wonderful script that I picked-up from the Thebiccountant.com (link is in the file). I tweaked it a bit so that it matches the outcome you desire, taking no credit for it.
It's actually meant to explode a Bill of Materials with quantities into a list of all the individual items used. But it works the same for your list of campaigns. Kindly refer to the link in the file to learn more about the techniques used.
