cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors