Getting all child values for every parent value based on a column

[rp2022]

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	140	F
15	16	E
16	160	F
17	18	E
18	19	E
19	20	E
21	20	E
20	200	F

Suppose i filter for Parent: 8, I should be able to see : 

8	9	E
9	10	E
10	11	E
11	110	F

E - means it has a further sub or child, F means it is the last node. How can i model the Power Query to get this result ?

I have this code, but it wont work when i have duplicates in Parent / Child

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}}),
RenamedColumns = Table.RenameColumns(#"Changed Type",{{"Parent", "Name"}, {"Child", "Value"}}),
R = Record.FromTable(RenamedColumns),
HierarchyLevelLimit = 5,
StepBack = RenamedColumns,
Ad_GenLev = Table.AddColumn(StepBack, "GenLev", each
List.Generate(
()=> [ y = [Name], z = {y} ],
each [y] <> null and (if List.Contains({0, ""}, HierarchyLevelLimit) then true else List.Count([z]) <= HierarchyLevelLimit + 1),
each [ y = Record.FieldOrDefault(R, [y], null), z = [z] & {y} ],
each [y] ), type {text} ),
Ad_Levels = Table.AddColumn(Ad_GenLev, "Levels", each
[ a = List.Zip({ {"1"..Text.From(List.Count([GenLev])-1)}, List.Reverse(List.Skip([GenLev])) }),
b = List.Accumulate(a, #table(type table[Child=text], {{[Name]}}), (st, cur)=> Table.AddColumn(st, "Level_" & cur{0}, (x)=> cur{1}, type text))
][b], type table ),
CombinedLevels = Table.Combine(Ad_Levels[Levels])
in
CombinedLevels

[rp2022 · Accepted solution]

 my solution at : Parent-Child Hierarchies with multiple parents in Power BI with Power Query

[dufoq3]

Hi @rp2022, another solution:

(Parent and Child columns must be as text so ChangedType step is important)

Output:

let
    Source = Expression.Evaluate("Table.FromRows(List.Transform(Text.Split(""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;140;F|15;16;E|16;160;F|17;18;E|18;19;E|19;20;E|21;20;E|20;200;F"",""|""), each Text.Split(_, "";"")), {""Parent"",""Child"",""Type""})", #shared),
    ChangedType = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Child", type text}, {"Type", type text}}),

    Grouped = Table.Group(ChangedType, {"Parent"}, {{"T", each _, type table}}),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Grouped))),
    F = (parent)=>
    Table.Combine(List.Generate(
        ()=> [ par = Text.From(parent), t = Record.FieldOrDefault(R, par, #table(null, {})) ],
        each not Table.IsEmpty([t]),
        each [ par = [t][Child], t = Table.Combine(List.Transform(par, (r)=> Record.FieldOrDefault(R, r, #table(null, {})))) ],
        each [t])),
    Result = F(8)
in
    Result

[rp2022]

@dufoq3 :Thank you for taking the time to answer, But the "8" filter needs to be dynamic, a slicer in the front end that the user can select and should be able to see these results.

[dufoq3]

If you mean slicer in excel then it is possible, but if you mean power bi slicer then you have to create dax solution instead of power query.

[rp2022]

@dufoq3 : yes I mean PBI Slicer, Any help with the DAX ?

[wdx223_Daniel]

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

[Akash_Varuna]

Hi @rp2022 I think you could add a function to fetch all child values recursively, starting from the given parent, and combine the results for hierarchical relationships.Something like this

(GetChildren as function) =>
let
    GetChildren = (ParentID as text, DataTable as table) =>
    let
        DirectChildren = Table.SelectRows(DataTable, each [Parent] = ParentID),
        FurtherChildren = Table.Combine(
            List.Transform(
                DirectChildren[Child], 
                each @GetChildren(_, DataTable)
            )
        ),
        Result = Table.Combine({DirectChildren, FurtherChildren})
    in
        Result
in
    GetChildren

[rp2022]

@Akash_Varuna : Thank you for taking the time to help me out. So, the filter will be in a visual and not on the dataset itself. If in the slicer the user selected "8" for instance, they need to see all the lines: 

8	9	E
9	10	E
10	11	E
11	110	F