Forum Discussion
Gauthier_cnl
4 years agoRegular Visitor
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...
- 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"
CNENFRNL
Community Champion
4 years agolet
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"