Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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!
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.
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!
User | Count |
---|---|
80 | |
76 | |
70 | |
69 | |
54 |
User | Count |
---|---|
104 | |
99 | |
92 | |
78 | |
69 |