Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!