Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register 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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |