Forum Discussion
Identify All Child
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 |
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
DistinctSté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
3 Replies
Hello Anonymous ,
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
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
DistinctSté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
