How can i get all the sub-children?-CodePudding

Hello guys, so i have a function ("flecheD"),


(ColChild,ColParent,ParentActuel,source)=>
                    let
                    mylist=Table.Column(Table.SelectRows(source,each Record.Field(_,ColParent)=ParentActuel),ColChild),
                    resultat=Text.Combine(mylist)
                    in
                   
                   Text.Trim(
                    if resultat ="" then ""  else @ resultat &"|" & @ flecheD(ColChild,ColParent,resultat,source),"|")

which loops through 2 columns (Parent,Child) to get all children of the main parent (output->Children column). The problem is that when the function is confronted with several children, the resultat variable no longer has a single letter/child but several, which blocks the function from looking for the other sub-children.

In order to solve this, I tried to create a custom function "SubChilldren" with List.Generate()

(children as text, ColChild,ColParent,source)=>
let 
i = 1,
length = Text.Length(children),

subchildren =  List.Generate( ()=>@flecheD(ColChild,ColParent,Text.At(children,i-1),source),   i<=length, i 1  )
in 
Text.Combine(subchildren)

which when coupled with my initial function


(ColChild,ColParent,ParentActuel,source)=>
                    let
                    mylist=Table.Column(Table.SelectRows(source,each Record.Field(_,ColParent)=ParentActuel),ColChild),
                    resultat=Text.Combine(mylist)
                    in
                   
                   Text.Trim(
                    if resultat ="" then ""  else if Text.Length(resultat) = 1 then  @ resultat &"|" & @ flecheD(ColChild,ColParent,resultat,source)
                    else     @resultat &"|"& SubChildren(resultat,ColChild,ColParent,source),"|")

should normally get the sub-children of each children. However, it doesnt work . Could you please help me . Thx

CodePudding user response：

I thought this was a fun way, but you could write a recursive function as well. I have it hard coded to 4 levels of children deep

(not sure how in your source data D child can have two parents, c and J, but whatever)

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   
#"Grouped Rows" = Table.Group(Source, {"Parent"}, {{"data", each List.RemoveNulls(_[Child]), type list}}),
Parent_List = List.Buffer(#"Grouped Rows"[Parent] ),
Child_List = List.Buffer(#"Grouped Rows"[data] ),

Process =  (n as list) as list =>
   let children = List.Transform(List.Transform(n, each  Text.ToList(_)), each Text.Combine( List.Distinct(List.Combine(List.Transform(_, each try Child_List{List.PositionOf( Parent_List, _ )} otherwise null))))) in children,

Level1=Process(Source[Parent]),
Level2=Process(Level1),
Level3=Process(Level2),
Level4=Process(Level3),
Final=List.Transform(List.Positions(Level1),each Level1{_}&"|"&Level2{_}&"|"&Level3{_}&"|"&Level4{_}&"|"),
#"Replaced Value" = Table.ReplaceValue(Table.FromList(Final),"||","",Replacer.ReplaceText,{"Column1"}),
custom1 = Table.ToColumns(Source) & Table.ToColumns(#"Replaced Value"),
custom2 = Table.FromColumns(custom1,Table.ColumnNames(Source) & {"Children"})
in custom2