Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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(_,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 result variable no longer has a single letter/child but several, which blocks the function from looking for the other children. Can you help me to solve this problem thanks,Capture d'écran_20221205_183438.png

 

file :https://docs.google.com/spreadsheets/d/1H0cI9XkgkOQ8R0cI2aE35GmEiV_U_zv4/edit?usp=sharing&ouid=10524... 

 

@Anonymous 

1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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

ppm1_0-1670465769957.png

 

(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

Microsoft Employee

View solution in original post

3 REPLIES 3
ppm1
Solution Sage
Solution Sage

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

ppm1_0-1670465769957.png

 

(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

Microsoft Employee
v-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

Please see if this document can help: Loops in Power Query M language (potyarkin.ml)

Best Regards,
Community Support Team _ kalyj

Anonymous
Not applicable

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=10431... 

@Gop01

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors