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-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @lbendlin  provided, and i want to offer some more information for user to refer to.

hello @Dadoge , based on your description, you can create a blank query and input the following code to advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjZU0lGCIwMFpVidaBDL2QguaAgXM8YUAwmbQGgjJDGQsCmQMCZCnTmaOpCYBbKYsyV2ew0NsBtoaIhus6ERDpXGcJXOhiYoIQG33NAUh+1mOMzE5h9DqIdiAQ==", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level 1", type text}, {"Level 2", type text}, {"Level 3", type text}, {"Level 4", type text},{"Hierachy Level",type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Level 1", "Level 2", "Level 3", "Level 4"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Level 1", "Level 2", "Level 3", "Level 4"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Filled Down",each [Level 2],each if [Hierachy Level]>=1 then  [Level 2] else "" ,Replacer.ReplaceValue,{"Level 2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Level 3],each if [Hierachy Level]>=2 then  [Level 3] else "",Replacer.ReplaceValue,{"Level 3"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",each [Level 4],each if [Hierachy Level]=3 then  [Level 4] else "",Replacer.ReplaceValue,{"Level 4"})
in
    #"Replaced Value3"

Output

Best Regards!

Yolo Zhu

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

[Dadoge]

Thank you very much, it totally work !

[lbendlin]

You would have to use Fill Down but with breaks.

Fill values in a column - Power Query | Microsoft Learn

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Dadoge]

Thank you for your answer !