Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
41 | |
23 | |
21 | |
20 | |
13 |
User | Count |
---|---|
128 | |
58 | |
48 | |
28 | |
20 |