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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
androo
Advocate II
Advocate II

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 II
Advocate II

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors