Forum Discussion

tatmaninov's avatar
tatmaninov
Frequent Visitor
1 year ago
Solved

How to list all direct and indirect linked values

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

FromTo
AB
BC
CD
CE
XY
YZ

 

And output this

FromTo
A{B,C,D,E}
X{Y,Z}

 

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

 

Need Help
  • ZhangKun's avatar
    ZhangKun
    1 year ago
    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

2 Replies

  • ZhangKun's avatar
    ZhangKun
    Icon for Super User rankSuper User
    1 year ago
    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