Forum Discussion
Rearranging columns with hierarchy
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.
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.