Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
assuming I have a table like this:
Father | Son |
A | B |
B | C |
C | D |
D | E |
E | F |
F | G |
G | H |
I would like to merge this table to obtain:
Father | Son |
A | B |
A | C |
A | D |
A | E |
A | F |
A | G |
A | H |
B | C |
B | D |
B | E |
B | F |
B | G |
B | H |
... |
how can it be achieved?
thanks
Solved! Go to Solution.
let
Source = your_table,
rec = Record.FromList(Source[Son], Source[Father]),
back_to_back = (s, name) =>
[next_name = Record.FieldOrDefault(rec, name),
next = if next_name is null then s else @back_to_back(s & {next_name}, next_name)][next],
add_col = Table.AddColumn(Table.RemoveColumns(Source, {"Son"}), "Son", (x) => back_to_back({}, x[Father])),
expand = Table.ExpandListColumn(add_col, "Son")
in
expand
I adapted a function I already had. Not as slick as @AlienSx ...
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
getParents = (SourceTable as table, CurrentRecord as record, KeyFieldName as text, ParentFieldName as text) =>
let
Parents = Table.SelectRows(SourceTable, each Record.Field(_, KeyFieldName) = Record.Field(CurrentRecord, ParentFieldName))
in
if Table.RowCount(Parents) = 1 then // multiple parents not allowed!
let
parent = Parents{0}
in
Table.Combine({@getParents(SourceTable, parent, KeyFieldName, ParentFieldName),Parents})
else
Table.FromRecords({})
,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Father", type text}, {"Son", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Fathers", each List.Combine({{[Father]},try getParents(#"Changed Type", _, "Son", "Father")[Father] otherwise {}})),
#"Expanded Fathers1" = Table.ExpandListColumn(#"Added Custom", "Fathers")
in
#"Expanded Fathers1"
let
Source = your_table,
rec = Record.FromList(Source[Son], Source[Father]),
back_to_back = (s, name) =>
[next_name = Record.FieldOrDefault(rec, name),
next = if next_name is null then s else @back_to_back(s & {next_name}, next_name)][next],
add_col = Table.AddColumn(Table.RemoveColumns(Source, {"Son"}), "Son", (x) => back_to_back({}, x[Father])),
expand = Table.ExpandListColumn(add_col, "Son")
in
expand
let
data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcZJDQAwDAMwLnmXxNZrHKryp7EoH8szODBcrA01uOZcaMGlllxpxbXW3MPuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Father = _t, Son = _t]),
recData = Record.FromList(data[Son], data[Father]),
result =
Table.FromRows(
List.Combine(
List.Transform(
data[Father],
(f) =>
let
a =
List.Generate(
() => f,
each not(Record.FieldOrDefault(recData, _, null) is null),
each Record.FieldOrDefault(recData, _),
each Record.FieldOrDefault(recData, _)
),
b = List.Zip({List.Repeat({f}, List.Count(a)), a})
in
b
)
),
Table.ColumnNames(data)
)
in
result
AlienSx's answer was so concise and brilliant, it gave me some inspiration to make the code less complicated
let
data = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcZJDQAwDAMwLnmXxNZrHKryp7EoH8szODBcrA01uOZcaMGlllxpxbXW3MPuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Father = _t, Son = _t]),
recData = Record.FromList(data[Son], data[Father]),
fx = (v) =>
List.Generate(
() => v,
each not(Record.FieldOrDefault(recData, _, null) is null),
each Record.FieldOrDefault(recData, _),
each Record.FieldOrDefault(recData, _)
),
result =
Table.ExpandListColumn(
Table.AddColumn(
data[[Father]],
"Son",
each fx([Father])
),
"Son"
)
in
result
Am I interpreting this correctly: A row in the result table means: The father is the father, grand father or grand grand father etc. of the son? Otherwise it would just be a carthesian product of the table with itself...
I'll have a look if I can solve this...
You can solve it manually, by keeping on merging Father with Son, Son1,Son 2,... until you get null results.
then you can pivot the columns and you have it.
the problem is that it is not dynamic. if there number of sons changes, you will have to update the query accordingly.
thanks for your engagement @PwerQueryKees