Decomposition Tree Visual & Data Preparation

[Dadoge]

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,

[v-cgao-msft]

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]

Hello @v-cgao-msft,

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 ?