Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
androo
Advocate III
Advocate III

Flatten Parent Child Hierarchy

hello,

 

I am looking to get the following result (P is Parent, C is Child).

 

P_IDP_Name2C_ID_1C_ID_2C_ID_3C_ID_4C_Name_1C_Name_2C_Name_3C_Name_4
1One264   A   
2Two154246  BC  
3Three263213182103DDDD
213D1001   E   

 

from this data (in the real data I don't know how many columns the result will need, though I suppose it could be easily established and provided as a parameter).

 

P_IDP_NameC_NameC_ID
1OneA264
2TwoB154
2TwoC246
3ThreeD263
3ThreeD213
3ThreeD182
3ThreeD103
213DE1001

 

I have seen several videos and posts that show me how to get this,

 

P_IDP_NameC_ID1C_Name1C_ID2C_Name2HierarchyPathHierarchyNodeIDHierarchyLevelIsLeafLevel
1One    111FALSE
2Two    221FALSE
3Three    331FALSE
1One264A  1|2642642TRUE
2Two154B  2|1541542TRUE
2Two246C  2|2462462TRUE
3Three263D  3|2632632TRUE
3Three213D  3|2132132FALSE
3Three182D  3|1821822TRUE
3Three103D  3|1031032TRUE
3Three213D1001E3|213|100110013TRUE

 

But, that isn't what I want. Perhaps I'm using the wrong search terms. Any help appreciated.

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

Hello, @androo 

let
    Source = your_table,
    f = (tbl as table) =>
        [count = Table.RowCount(tbl),
        lst = List.Buffer(List.Transform({1..count}, Text.From)),
        ids = Record.FromList(tbl[C_ID], List.Transform(lst, (x) => "C_ID_" & x)),
        names = Record.FromList(tbl[C_Name], List.Transform(lst, (x) => "C_Name_" & x)),
        res = [id = ids, name = names]][res],
    g = Table.Group(Source, {"P_ID", "P_Name"}, {{"all", f}}),
    expand = Table.ExpandRecordColumn(g, "all", {"id", "name"}),
    id_cols = List.Distinct(List.Combine(List.Transform(expand[id], Record.FieldNames))),
    name_cols = List.Distinct(List.Combine(List.Transform(expand[name], Record.FieldNames))),
    expand_id = Table.ExpandRecordColumn(expand, "id", id_cols),
    expand_name = Table.ExpandRecordColumn(expand_id, "name", name_cols)
in
    expand_name

View solution in original post

2 REPLIES 2
androo
Advocate III
Advocate III

Thanks very much. With some very minor tweaking that has worked very well.

AlienSx
Super User
Super User

Hello, @androo 

let
    Source = your_table,
    f = (tbl as table) =>
        [count = Table.RowCount(tbl),
        lst = List.Buffer(List.Transform({1..count}, Text.From)),
        ids = Record.FromList(tbl[C_ID], List.Transform(lst, (x) => "C_ID_" & x)),
        names = Record.FromList(tbl[C_Name], List.Transform(lst, (x) => "C_Name_" & x)),
        res = [id = ids, name = names]][res],
    g = Table.Group(Source, {"P_ID", "P_Name"}, {{"all", f}}),
    expand = Table.ExpandRecordColumn(g, "all", {"id", "name"}),
    id_cols = List.Distinct(List.Combine(List.Transform(expand[id], Record.FieldNames))),
    name_cols = List.Distinct(List.Combine(List.Transform(expand[name], Record.FieldNames))),
    expand_id = Table.ExpandRecordColumn(expand, "id", id_cols),
    expand_name = Table.ExpandRecordColumn(expand_id, "name", name_cols)
in
    expand_name

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.