Forum Discussion

Gauthier_cnl's avatar
Gauthier_cnl
Regular Visitor
4 years ago
Solved

Hierarchy parent child problem

Hi,   I'm working with parent-child hierarchy and I want do to do something but I'm not sure if it's possible. Here an example of what I'm trying to do :   There are 4 columns : ID, parent I...
  • CNENFRNL's avatar
    4 years ago
    let
        REC = (l as list, id) as list =>
            if id is null then l else
                let
                    p = List.PositionOf(Sup, id)            
                in if p<>-1 then REC(l & {id}, Init{p}) else l & {id},
    
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKiCqVYnWglIyALJFAJ5hlDeVVgngmQBZIvB/NMgSyQiFtRampVZl46WNAMKpgG5pkDWSAjisE8QwOIRfkQi0A8sBCKAcYgEbBcIcROOD8ZYiuICRYsU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ID_INIT = _t, PROCESS = _t]),
        Sup = Source[ID],
        Init = Source[ID_INIT],
        #"Added Path" = Table.AddColumn(Source, "Path", each {[ID]} & REC({}, [ID_INIT])),
        #"ID Freezing" = let id = #"Added Path"[ID] in List.Transform(List.PositionOf(#"Added Path"[PROCESS], "Freezing", 2), each id{_}),
        #"All Freezing" = List.Distinct(List.Combine(List.Select(#"Added Path"[Path], each List.Count(List.Intersect({_, #"ID Freezing"}))>0))),
        #"Replaced Value" = Table.ReplaceValue(#"Added Path", each [Path], each if List.Contains(#"All Freezing", [ID]) then "A" else "", Replacer.ReplaceValue, {"Path"})
    in
        #"Replaced Value"