Identify All Child

Hi Anonymous 

With AlienSx back_to_back function

https://community.fabric.microsoft.com/t5/Power-Query/Merge-table-in-loop/m-p/4115641#M135009

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1YlWMgIyDMEsYzjLBMgyArNMgSxjMMsMrCEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Parent Contract ID" = _t]),
Replace = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Parent Contract ID"}),

rec = Record.FromList(Replace[Parent Contract ID], Replace[Contract ID]), 
back_to_back = (s, name) => 
   [next_name = Record.FieldOrDefault(rec, name),
   next = if next_name is null or next_name ="" then s else @back_to_back(s & {next_name}, next_name)][next],

Add_col = Table.AddColumn(Replace, "Child Contracts1", (x) => back_to_back({}, x[Contract ID])),
Expand = Table.ExpandListColumn(Add_col, "Child Contracts1"),
Join = Table.NestedJoin(Expand, {"Contract ID"}, Expand, {"Child Contracts1"}, "Expand", JoinKind.LeftOuter),
#"Child Contracts" = Table.AddColumn(Join, "Child Contracts", each Text.Combine([Expand][Contract ID],", ")),
RemoveColumns = Table.RemoveColumns(#"Child Contracts",{"Child Contracts1", "Expand"}),
Distinct = Table.Distinct(RemoveColumns, {"Contract ID"})
in
Distinct

 Stéphane

Hi Anonymous, same approach as slorin, but without recursive function:

Output:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVKK1YlWMgIyDMEsYzjLBMgyArNMgSxjMMsMrCEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Parent Contract ID" = _t]),
Replace = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Parent Contract ID"}),
    Buffer = Table.Buffer(Table.SelectColumns(Replace,{"Contract ID", "Parent Contract ID"})),
    Ad_Childs = Table.AddColumn(Replace, "Childs", each 
        List.Generate(
                ()=> Table.SelectRows(Buffer, (x)=> x[Contract ID] = [Contract ID]){0}?[Parent Contract ID]?,
                each _ <> null,
                each Table.SelectRows(Buffer, (x)=> x[Contract ID] = _){0}?[Parent Contract ID]?), type list),
    ExpandedChilds = Table.ExpandListColumn(Ad_Childs, "Childs"),
    MergedQueries = Table.NestedJoin(ExpandedChilds, {"Contract ID"}, ExpandedChilds, {"Childs"}, "ExpandedChilds", JoinKind.LeftOuter),
    Ad_ChildContracts = Table.AddColumn(MergedQueries, "Child Contracts", each Text.Combine([ExpandedChilds][Contract ID], ", "), type text),
    RemovedColumns = Table.RemoveColumns(Ad_ChildContracts,{"Childs", "ExpandedChilds"}),
    RemovedDuplicates = Table.Distinct(RemovedColumns, {"Contract ID"})
in
    RemovedDuplicates