Forum Discussion
Create Parent - Sub level Hierarchy from spaces
Hello everyone, I was researching how to solve this problem, but I couldn't solve it 😞
The tasks can contain multiple subtasks. The initial task does not contain spaces. The Sub-tasks are subdivided by multiples of 2 spaces.
E.g
Parentlevel = No space
Sublevel_1 = 2 spaces
Sublevel_2 = 4 spaces
Sublevel_3 = 6 spaces
Sublevel_4 = 8 spaces
Sublevel_5 = 10 spaces
How can I convert this is to a PowerBI hierachy?
From this:
| Activity Name | Activity ID |
| Plan | 1 |
| HITOS type1 | 2 |
| Hitos A | 3 |
| Hito A1 | 4 |
| HA1-A | 5 |
| HA1-B | 6 |
| Hito A2 | 7 |
| Hito A3 | 8 |
| HITOS type2 | 9 |
| Hitos B | 10 |
| Hito B1 | 11 |
| HB1-A | 12 |
TO this:
| Activity Name | Activity ID | ID Parent ID |
| Plan | 1 | null |
| HITOS type1 | 2 | 1 |
| Hitos A | 3 | 2 |
| Hito A1 | 4 | 3 |
| HA1-A | 5 | 4 |
| HA1-B | 6 | 4 |
| Hito A2 | 7 | 3 |
| Hito A3 | 8 | 3 |
| HITOS type2 | 9 | 1 |
| Hitos B | 10 | 9 |
| Hito B1 | 11 | 10 |
| HB1-A | 12 | 11 |
I share the query to paste in the advanced editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJzFPSUTJUitWJVlJQ8PAM8Q9WKKksSFUwBAobQYWBEpkl+cUKjkAxY7gYRFTBEaTSBEkUKO5oqAtSa4oh6gQUNcMwwQgoao4hagwUtcB0GEixJZrDQMYaGqCb4ARymaEhqiOcIE4zNEIRhkpApIyxSoHtMMEwDCxsikWHE8QwM6xSYF1AL8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Activity Name" = _t, #"Activity ID" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Activity Name", type text}, {"Activity ID", Int64.Type}})
in
#"Changed Type"
Any ideas?
2 Replies
Hi Anonymous ,
According to your description, I suggest that you can create a index column in Power Query. And then create a custom column according to the index to return "Parent ID".
Please refer to the solution in the link below.
Solved: Create Parent-Child Hierarchy from indentations - Microsoft Power BI Community
Best Regards,
Community Support Team _ xiaosunIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
