Flatten Parent Child Hierarchy

[androo]

hello,

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

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

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_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

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

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

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

[androo]

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

[AlienSx · Accepted solution]

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