Decomposition Tree Visual & Data Preparation

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.