Forum Discussion

MartinFO's avatar
MartinFO
New Member
5 years ago
Solved

Create Parent-Child Hierarchy from indentations

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 NameActivity ID 
Technical management39061 
  Specification Overview38544 
Design39057 
  Concept38559 
  3D38547 
  DFMEA38556 
  Validation plan38558 

 

To this:

Activity NameActivity IDParent ID
Technical management39061 
Specification Overview3854439061
Design39057 
Concept3855939057
3D3854739057
DFMEA3855639057
Validation plan3855839057
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

3 Replies

  • MartinFO's avatar
    MartinFO
    New Member
    5 years ago

    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.

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.