Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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
@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.
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
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
@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 |
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.