Identify All Child

[Kat_00]

Hi everyone,

I’m having trouble with Power Query and could use some help. I’m trying to create a function in the Advanced Editor to add a new column that lists all child contracts for each contract, as shown in the example table below.

Does anyone have suggestions on how to get this working?

Thanks in advance!

Contract ID	Parent Contract ID	Child Contracts
1		2, 3, 4, 5
2	1	4
3	1	5
4	2
5	3
6

[slorin · Accepted solution]

Hi @Kat_00 

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

[dufoq3 · Accepted solution]

Hi @Kat_00, 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

[slorin · Accepted solution]

Hi @Kat_00 

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

[dharmendars007]

Hello @Kat_00 , 

Can you help us with with exact logic you are trying to implement in child contract columns, thsi will help us to check further.

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN