Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.