Forum Discussion

Dadoge's avatar
Dadoge
Frequent Visitor
2 years ago

Decomposition Tree Visual & Data Preparation

Hello,

 

I want to make a Tree Visual and hence need to prepare my data but I'm having trouble to do so.

 

Data Structure: I have several columns named 'Level 1', 'Level 2', 'Level 3', and 'Level 4'. Each column represents a different level of hierarchy, where 'Level 2' is tied to 'Level1',  'Level 3' is tied to 'Level 2', and 'Level 4' to 'Level 3'. One level is tied to another when there is a break between each rows like this :

Here, Company 2 and Company 3 are tied to Company 1. Company 4 is tied to Company 3 but not Company 2.

 

 

What I aim to achieve is having the table on the right. Each different Level 1 is considered a new branch of the Tree.  The Level in a branch all have the same contract number.

 

Thanks in advance,

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Dadoge ,

    Try grouping and then filling down:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc85DoAgEEDRq5ipKdiX2mMQCg/g0np7BWQcCYUJJCwv5BMjzPt6LNs5CWCAg99TSKWNzQtILObTRiU6QZ3snRo71Ryhum4ldZo4Qk15gjhTXLu2/T+cD9XZvs99+tC5QZ9/+9D5cV94+tCFn05wSOkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level 1" = _t, #"Level 2" = _t, #"Level 3" = _t, #"Level 4" = _t, #"Hierachy Level" = _t, #"Contract Number" = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level 1", type text}, {"Level 2", type text}, {"Level 3", type text}, {"Level 4", type text}, {"Hierachy Level", Int64.Type}, {"Contract Number", Int64.Type}, {"Index", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Hierachy Level", "Contract Number", "Index"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","",null,Replacer.ReplaceValue,{"Attribute", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute]), "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Contract Number"}, {{"Data", each Table.FillDown(_,{"Level 1","Level 2","Level 3","Level 4"})}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Hierachy Level", "Index", "Level 1", "Level 2", "Level 3", "Level 4"}, {"Hierachy Level", "Index", "Level 1", "Level 2", "Level 3", "Level 4"})
in
    #"Expanded Data"

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

    • Dadoge's avatar
      Dadoge
      Frequent Visitor
      2 years ago

      Hello Anonymous,

       

      First of all thank you for your quick answer !

       

      I forgot to add the usecase where in a level 1, multiple level can contains level as shown below. I tried to adjust your code but the filling doesnt match with what i want. I feel like the index is crucial in what i want to achieve because of the order.  Can you help me on this ?