Create Parent-Child Hierarchy from indentations

[MartinFO]

Hi all

I use a project tool called EasyProject.org, where I can download a report. The tasks in a project can contain multiple subtasks. The subtask level is marked by leading "spaces" in the name. The structure seems to be that first space indicates "this is a subtask" subsequent spaces are the level. E.g. Parentlevel = No space, Sublevel1 = 2 spaces, Sublevel2 = 3 spaces, etc. How can I convert this is to a PowerBI hierachy?

I think I need to create a table with parent ID, in order to use PATH, see below example. But I cannot figure out how to do this based on indentations. It also seems like the first space, is not really a "space", since replacing "2 x space" does not work. Any ideas? 

From this:

Activity Name	Activity ID
Technical management	39061
Specification Overview	38544
Design	39057
Concept	38559
3D	38547
DFMEA	38556
Validation plan	38558

To this:

Activity Name	Activity ID	Parent ID
Technical management	39061
Specification Overview	38544	39061
Design	39057
Concept	38559	39057
3D	38547	39057
DFMEA	38556	39057
Validation plan	38558	39057

[v-alq-msft · Accepted solution]

Hi, @MartinFO 

Based on your descirption, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may try the following transformations in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklNzsjLTE7MUchNzEtMT81NzStR0lEytjQwM1SK1YlWUlAILkhNzkwDqinJzM9T8C9LLSrLTC0HKbIwNTEBK3JJLc5Mz4PoMzWH6nPOz0tOLSiBKDS1hIoau0B1wpS5uPm6OkIVmUHFwhJzMlMg9hXkJOZBZS2UYmMB", 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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let activityname=[Activity Name],activityid=[Activity ID],index=[Index] in
if Text.StartsWith([Activity Name]," ")
then 
Table.Max(Table.SelectRows(#"Added Index",each [Index]<index and not Text.StartsWith([Activity Name]," ")),"Index")[Activity ID]
else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    Custom1 = Table.TransformColumns(#"Removed Columns",{"Activity Name",each Text.TrimStart(_)})
in
    Custom1

Result:

Best Regards

Allan

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

[Jimmy801]

Hello @MartinFO 

try out this code. It uses a custom function. I don't know how the performance of this is

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklNzsjLTE7MUchNzEtMT81NzStR0lEytjQwM1SK1YlWUlAILkhNzkwDqinJzM9T8C9LLSrLTC0HKbIwNTEBK3JJLc5Mz4PoMzWH6nPOz0tOLSiBKDS1hIoau0B1wpS5uPm6OkIVmUHFwhJzMlMg9hXkJOZBZS2UYmMB", 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}}),
    GetFather=
    (tTable as table) =>
    let
        //Table has to contain [Material] and [Level]
        tTableInternal = Table.Buffer(tTable),
        MaterialList = List.Buffer(tTableInternal[Activity ID]),
        LevelList = List.Buffer(tTableInternal[Level]),
        CreateListForNewColumn = List.Skip(List.Generate
        (
            ()=>
            [
                
                CurrentIndex = 0, 
                CurrentData = [1= "", 2= "", 3= "", 4= "", 5="", 6= "", 7= "", 8="", 9="", 10= "", 11= ""],
                sFather = ""
                
                
            ],
            each [CurrentIndex]<= List.Count(MaterialList),
            (x)=>
            
            [
                
                CurrentData = Record.TransformFields(x[CurrentData],{Text.From(LevelList{x[CurrentIndex]}),each Text.From(MaterialList{x[CurrentIndex]})}), 
                sFather = if LevelList{x[CurrentIndex]}= 1 then "first level" else Record.Field(x[CurrentData], Text.From(LevelList{x[CurrentIndex]}-1)),
                CurrentIndex = x[CurrentIndex]+1
            ],
            each [sFather]
            
        ),1),

        NewTableColumns = Table.ToColumns(tTable )&{CreateListForNewColumn },
        NewTableFinal=Table.FromColumns(NewTableColumns , Table.ColumnNames(tTable)&{"Father"})

    in
        NewTableFinal,
    
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Level", each if Text.StartsWith([Activity Name], "  ") then 2 else if Text.StartsWith([Activity Name], "   ") then 3 else 1),
    AddPriorLevel = GetFather(#"Added Custom")
in
    AddPriorLevel

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[MartinFO]

I found out that I can split the columns by "Non-Breaking space" #(00A0). But now to I take parent ID, I would need script.