Create Parent - Sub level Hierarchy from spaces

[Anonymous]

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?

[v-xiaosun-msft]

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 _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi, thank you for answering! the previous solution did not work for me since it only works for 2 levels... I based on that question to formulate this new one since my English is horrible. thank you so much!