How to list all direct and indirect linked values

[tatmaninov]

I am working in Excel Power Query and I am trying to take a table that details relationships between two values example below

From	To
A	B
B	C
C	D
C	E
X	Y
Y	Z

And output this

From	To
A	{B,C,D,E}
X	{Y,Z}

Would really appreciate a steer or pointers about how I can do this.

[tatmaninov]

Amazing!, thank you!

[ZhangKun · Accepted solution]

let
    dataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJSitWJBpI6Ss5gljOQ5QJnuYJZEUBWJJgVCWRFKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),
    fx = (tbl, s) => 
        let 
            a = List.Combine(List.Transform(s, (v) => Table.SelectRows(tbl, each [From] = v)[To]))
        in 
            if List.Count(a) = 0 then {} else (a & @Fx(tbl, a)), 
    result = Table.AddColumn(
        Table.SelectRows(dataTable, each Table.RowCount(Table.SelectRows(dataTable, (r) => r[To] = [From])) = 0)[[From]], 
        "To", 
        each "{" & Text.Combine(fx(dataTable, {[From]}), ",") & "}"
    )
in
    result