Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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,
@Anonymous
Solved! Go to Solution.
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
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
Hi @Anonymous ,
Please see if this document can help: Loops in Power Query M language (potyarkin.ml)
Best Regards,
Community Support Team _ kalyj
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
12 |