Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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.
Solved! Go to 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
Thanks very much. With some very minor tweaking that has worked very well.
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
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
20 | |
11 | |
11 | |
8 | |
7 |