Flatten Parent Child Hierarchy

Question

hello,&nbsp;I am looking to get the following result (P is Parent, C is Child).&nbsp;P_IDP_Name2C_ID_1C_ID_2C_ID_3C_ID_4C_Name_1C_Name_2C_Name_3C_Name_41One264&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;2Two154246&nbsp;&nbsp;BC&nbsp;&nbsp;3Three263213182103DDDD213D1001&nbsp;&nbsp;&nbsp;E&nbsp;&nbsp;&nbsp;&nbsp;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).&nbsp;P_IDP_NameC_NameC_ID1OneA2642TwoB1542TwoC2463ThreeD2633ThreeD2133ThreeD1823ThreeD103213DE1001&nbsp;I have seen several videos and posts that show me how to get this,&nbsp;P_IDP_NameC_ID1C_Name1C_ID2C_Name2HierarchyPathHierarchyNodeIDHierarchyLevelIsLeafLevel1One&nbsp;&nbsp;&nbsp;&nbsp;111FALSE2Two&nbsp;&nbsp;&nbsp;&nbsp;221FALSE3Three&nbsp;&nbsp;&nbsp;&nbsp;331FALSE1One264A&nbsp;&nbsp;1|2642642TRUE2Two154B&nbsp;&nbsp;2|1541542TRUE2Two246C&nbsp;&nbsp;2|2462462TRUE3Three263D&nbsp;&nbsp;3|2632632TRUE3Three213D&nbsp;&nbsp;3|2132132FALSE3Three182D&nbsp;&nbsp;3|1821822TRUE3Three103D&nbsp;&nbsp;3|1031032TRUE3Three213D1001E3|213|100110013TRUE&nbsp;But, that isn't what I want. Perhaps I'm using the wrong search terms. Any help appreciated.

aliensx · Accepted Answer

Hello,&nbsp;androo&nbsp;let
    Source = your_table,
    f = (tbl as table) =&gt;
        [count = Table.RowCount(tbl),
        lst = List.Buffer(List.Transform({1..count}, Text.From)),
        ids = Record.FromList(tbl[C_ID], List.Transform(lst, (x) =&gt; "C_ID_" &amp; x)),
        names = Record.FromList(tbl[C_Name], List.Transform(lst, (x) =&gt; "C_Name_" &amp; 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

Forum Discussion

Flatten Parent Child Hierarchy

2 Replies

Related content

Power Query - flatten Parent-child hierarchy

Flattening of parent-child hierarchy in power query.

Parent/child hierarchy creation

Why would you flatten Parent Child Hierarchies?

Flattening Parent-Child Hierarchy for Bill of Materials

P_ID	P_Name2	C_ID_1	C_ID_2	C_ID_3	C_ID_4	C_Name_1	C_Name_2	C_Name_3	C_Name_4
1	One	264				A
2	Two	154	246			B	C
3	Three	263	213	182	103	D	D	D	D
213	D	1001				E

P_ID	P_Name	C_Name	C_ID
1	One	A	264
2	Two	B	154
2	Two	C	246
3	Three	D	263
3	Three	D	213
3	Three	D	182
3	Three	D	103
213	D	E	1001

P_ID	P_Name	C_ID1	C_Name1	C_ID2	C_Name2	HierarchyPath	HierarchyNodeID	HierarchyLevel	IsLeafLevel
1	One					1	1	1	FALSE
2	Two					2	2	1	FALSE
3	Three					3	3	1	FALSE
1	One	264	A			1\|264	264	2	TRUE
2	Two	154	B			2\|154	154	2	TRUE
2	Two	246	C			2\|246	246	2	TRUE
3	Three	263	D			3\|263	263	2	TRUE
3	Three	213	D			3\|213	213	2	FALSE
3	Three	182	D			3\|182	182	2	TRUE
3	Three	103	D			3\|103	103	2	TRUE
3	Three	213	D	1001	E	3\|213\|1001	1001	3	TRUE