Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How do I make this loop all children recursively?

Hello guys,    i have a function ("flecheD") (ColChild,ColParent,ParentActuel,source)=> let mylist=Table.Column(Table.SelectRows(source,each Record.Field(_,C...
Need Help
Tips and Tricks
Tutorial Requests
  • ppm1's avatar
    ppm1
    3 years ago

    Please try this function. Seems to be do what you are looking for.

     

    (inputtable as table, searchvalue as text, inputtext as text)=>
let 
input = searchvalue,
childrows = try Table.SelectRows(inputtable, each [Parent] = input)[Child] otherwise {},
output = if List.IsEmpty(childrows) then inputtext & Text.Combine(childrows, "") else Text.Combine(childrows, "") & Text.Combine(List.Transform(childrows, each @fnParentChild(inputtable, _, "")), "|")
in 
output

     

    If needed, you can see it with your data, you can put this in a blank query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIC4oAgfxcQ08s5VClWJ1rJGchxQYi71HiChUHKvbAo98Kt3BmLcpB4KBZxEMcTIQ40JBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t, Param = _t, Children = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Child", type text}, {"Param", type text}, {"Children", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let par = [Parent] in fnParentChild(#"Changed Type", par, ""))
in
    #"Added Custom"

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi v-yanjiang-msft , thx for your suggestion. So  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 still doesnt work ğŸ˜…. Could you please help me . Thx

     

    source link : https://docs.google.com/spreadsheets/d/1nbF7-ZBZNMCmHdzSqqfBdHCPNqdWfKGa/edit?usp=sharing&ouid=104317018191542815438&rtpof=true&sd=true 

    @Gop01