Forum Discussion
Solved
Getting all child values for every parent value based on a column
I have this data : Parent Child Type 1 3 E 2 102 F 3 5 F 3 4 E 4 6 E 7 170 F 8 9 E 9 10 E 10 11 E 11 110 F 12 120 F 13 14 E 14 1...
let
Source = Excel.Workbook(File.Contents("C:\Users\e768534\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Child", type text}, {"Parent", type text}}),
fx=(ParentID,HierarchyTable)=>
let
a=Table.SelectRows(HierarchyTable,each [Parent]=ParentID),
b=List.TransformMany(Table.ToRecords(a),each if [Type]="E" then {_}&@fx([Child],HierarchyTable) else {_},(x,y)=>y)
in
b,
Custom1=Table.FromRecords(fx(8,#"Changed Type"))
in
Custom1