Forum Discussion
Creating Hierarchy "Backwards"
Hi All,
I have activities which are classed in a WBS hierarchy, but, the hierarchy starts the opposite way to desired, and activities have a differing number of WBS levels, some have 2, others have 9, as such:
| Activity | wbs9 | wbs8 | wbs7 | wbs6 | wbs5 | wbs4 | wbs3 | wbs2 | wbs1 |
| 1 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 |
| 2 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 |
| 3 | 7 | 6 | 5 | 4 | 3 | 2 | 1 | ||
| 4 | 2 | 1 | |||||||
| 5 | 4 | 3 | 2 | 1 | |||||
| 6 | 5 | 4 | 3 | 2 | 1 | ||||
| 7 | 4 | 3 | 2 | 1 | |||||
| 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 | ||
| 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 | |
| 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 |
This is the desired output:
| Activity | wbs1 | wbs2 | wbs3 | wbs4 | wbs5 | wbs6 | wbs7 | wbs8 | wbs9 |
| 1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | ||
| 4 | 1 | 2 | |||||||
| 5 | 1 | 2 | 3 | 4 | |||||
| 6 | 1 | 2 | 3 | 4 | 5 | ||||
| 7 | 1 | 2 | 3 | 4 | |||||
| 8 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | ||
| 9 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | |
| 10 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
I have the initial table ready within PowerQuery, with thousands of rows.
Thanks for your help!
Hi,
I've solved it myself.
I simply replacead all NULL values with a "$", then concatenated all the columns (with £ delimiters), starting with the furthest right column from the initial table.
From there I replaced the $ with blank and split the column via the £ delimiter, and voilá, that was my hierarchy.
4 Replies
Hi Donerandparmo1,
If you want to create a hierarchy with columns starting wbs1 to wbs9, then right click on wbs1 column and select "Create hierarchy" and then keep adding columns upto wbs9 to the hierarchy inthe order as required. You can now use the newly created hierarchy in the visual directly.
Result -
If this is not the desired result, provide more detailed sample data of the tables for sharing better approach.
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics EngineerHi,
Thanks for the response.
This would not work as some activities go up to 9, and others to 2.
I need WBS1 to be the value from the furthest right column on the initial table, with a value, and then WBS2 to be the second furthest right column from that table, with a value.
Does that make sense?Cheers.
Based on the comments, the table visual should have below desired result -
Option1 - Create a hierarchy starting from WBS9 up till WBS1
Option2 - If you want to show the hierarchy dynamically from WBS9 to WBS1 based on the activities as some go upto 9 while others upto 2 - The solution is to have a Field Parameter created from WBS9 to 1 which will have slicer to choose.
Parameter = { ("wbs9", NAMEOF('Table'[wbs9]), 0), ("wbs8", NAMEOF('Table'[wbs8]), 1), ("wbs7", NAMEOF('Table'[wbs7]), 2), ("wbs6", NAMEOF('Table'[wbs6]), 3), ("wbs5", NAMEOF('Table'[wbs5]), 4), ("wbs4", NAMEOF('Table'[wbs4]), 5), ("wbs3", NAMEOF('Table'[wbs3]), 6), ("wbs2", NAMEOF('Table'[wbs2]), 7), ("wbs1", NAMEOF('Table'[wbs1]), 8) }If this is not the expectation, please share sample data in a pbix file and exact snapshots of expected results by case for sharing better solution.
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
Hi,
I've solved it myself.
I simply replacead all NULL values with a "$", then concatenated all the columns (with £ delimiters), starting with the furthest right column from the initial table.
From there I replaced the $ with blank and split the column via the £ delimiter, and voilá, that was my hierarchy.
