Forum Discussion
Helper IIGetting 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 | 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
8 Replies
- Akash_Varuna
Super User
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
- wdx223_Daniel
Community Champion
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 - 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- 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
