Forum Discussion
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,
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.
4 Replies
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-Forum/ba-p/963216
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/1447523Thank you for your answer !
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.
Thank you very much, it totally work !
