Flattening a hierarchical dimension table in powerbi powerquery

[CBO2404]

I have a hierarchical dimension table in Power BI with the columns id, parent, and child. I want to flatten this table so that for each parent I get a row with its leaf, and if it is already a leaf, then the parent is equal to the leaf. Does anyone have M code to achieve this in Power Query using AddColumn on the existing dimension table?

e.g. Table Dim_XY: 

parent	child
A	A
A	B
A	C
B	D
B	E
C	F
D	G

Desired result :

parent	leaf
A	G
A	E
A	F
B	G
B	E
C	F
D	G
E	E
F	F
G	G

[rohit1991 · Accepted solution]

Hi @CBO2404 

Below is a working script you can adapt (replace Dim_XY with your table name):

let
   Source = Dim_XY,
   ChildrenByParent = Table.Group(Source, {"parent"}, {{"children", each _[child], type list}}),
   GetChildren = (p as any) as list =>
       let r = Table.SelectRows(ChildrenByParent, each [parent] = p)
       in  if Table.IsEmpty(r) then {} else r{0}[children],
   Parents  = List.Distinct(Source[parent]),
   Children = List.Distinct(Source[child]),
   Nodes    = List.Distinct(Parents & Children),
   LeavesFrom = (n as any) as list =>
       let kids = GetChildren(n)
       in  if List.IsEmpty(kids) then {n}
           else List.Combine(List.Transform(kids, each LeavesFrom(_))),
   Pairs =
       List.Combine(
          List.Transform(
               Nodes,
               (n) => List.Transform(List.Distinct(LeavesFrom(n)), (lf) => [parent = n, leaf = lf])
           )
       ),
   Result = Table.FromRecords(Pairs),
   Sorted = Table.Sort(Result, {{"parent", Order.Ascending}, {"leaf", Order.Ascending}})
in
   Sorted

[Shahid12523]

let
Source = YourTable, // replace with your table name

GetLeaves = (p as text, tbl as table) as list =>
let
children = Table.SelectRows(tbl, each [parent] = p)[child],
result = if List.IsEmpty(children) then {p} else List.Combine(List.Transform(children, each GetLeaves(_, tbl)))
in result,

AllNodes = List.Distinct(List.Combine({Source[parent], Source[child]})),
Expanded = Table.ExpandListColumn(
Table.AddColumn(Table.FromList(AllNodes, Splitter.SplitByNothing(), {"parent"}), "leaf", each GetLeaves([parent], Source)),
"leaf"
)
in
Expanded

This will give you exactly the flattened parent → leaf mapping you showed.

[rohit1991 · Accepted solution]

Hi @CBO2404 

Below is a working script you can adapt (replace Dim_XY with your table name):

let
   Source = Dim_XY,
   ChildrenByParent = Table.Group(Source, {"parent"}, {{"children", each _[child], type list}}),
   GetChildren = (p as any) as list =>
       let r = Table.SelectRows(ChildrenByParent, each [parent] = p)
       in  if Table.IsEmpty(r) then {} else r{0}[children],
   Parents  = List.Distinct(Source[parent]),
   Children = List.Distinct(Source[child]),
   Nodes    = List.Distinct(Parents & Children),
   LeavesFrom = (n as any) as list =>
       let kids = GetChildren(n)
       in  if List.IsEmpty(kids) then {n}
           else List.Combine(List.Transform(kids, each LeavesFrom(_))),
   Pairs =
       List.Combine(
          List.Transform(
               Nodes,
               (n) => List.Transform(List.Distinct(LeavesFrom(n)), (lf) => [parent = n, leaf = lf])
           )
       ),
   Result = Table.FromRecords(Pairs),
   Sorted = Table.Sort(Result, {{"parent", Order.Ascending}, {"leaf", Order.Ascending}})
in
   Sorted

[CBO2404 · Accepted solution]

Thank you for your quick response. When I use the M code, I get the following error message

"Expression.Error: The name 'LeavesFrom' wasn't recognized. Make sure it's spelled correctly." asking copilot ther LeavesFrom must be like :

LeavesFrom = let fn = (n as any) as list => let kids = GetChildren(n) in if List.IsEmpty(kids) then {n} else List.Combine(List.Transform(kids, each @FN(_))) in fn,

[rohit1991]

Hi @CBO2404 

Replace the line where you used Combiner.CombineTextByDelimiter with:

Text.Combine(_, " > ")

That will give you the same result without throwing the error.