Power Query Function to create clusters

[wlknsnBI]

Hi,

I have network data which consists of all sorts of nodes connected to each other. This has a typical structure like a node table with "From" & "To" Records. The aim is to be able to filter this Node table so that I can pass a From or To string and get all associated records. It's actually similar to what the path function does which scans through the table and gets all parents of each nicely concatenates the results in a string in a specific order. In this case, the order is not that important, it's just about getting all related items into a single string.

Example data and expected result:

From	To	Result (all related combinations)
A		A-B-C-D-E-F-L
B		A-B-C-D-E-F-L
C		A-B-C-D-E-F-L
D		A-B-C-D-E-F-L
E		A-B-C-D-E-F-L
A	B	A-B-C-D-E-F-L
B	C	A-B-C-D-E-F-L
C	D	A-B-C-D-E-F-L
D	E	A-B-C-D-E-F-L
E	F	A-B-C-D-E-F-L
G		G-H-I
H		G-H-I
I		G-H-I
G	H	G-H-I
H	I	G-H-I
J		J
K		K
L		L
B	L	A-B-C-D-E-F-L

A dax solution would also be nice, but doubt this can be done and performance wise it may be too extreme (unless we could calculate from a selectedvalue if that selectedvalue is part of a cluster).

[CNENFRNL]

Simple enough with a recursion if performance is not of top priority.

let
    Recur = (res as list, lookup as text) =>
        let
            pos = List.PositionOf(Series[From], lookup)
        in
            if pos <> -1 then @Recur(res & {lookup}, Series[To]{pos}) else res & {lookup},
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcvLDcAgEAPRXnymifALJHSw2v7bSECavT3JYzNdSpInUwYFVNDAjnPUJfIafYtDP7q5DjDBnkZs8+hhfMEC/ceS+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),
    
    Series = Table.Distinct(Table.SelectRows(Source, each [To] <> "")),
    Diff = List.Difference(Series[From], Series[To]),
    Invoquation = List.Transform(Diff, each Recur({}, _)),
    Mapping = Table.AddColumn(Source, "Hierarchy", each try Text.Combine(List.Select(Invoquation, (l) => List.Contains(l, [From])){0}, "-") otherwise [From])
in
    Mapping

[smpa01 · Accepted solution]

@wlknsnBI  you can try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7JCcAwDARbCXpbRfg+OzDuv43Eij7B2YdAu8PCzEmWDF3PWXbsOXDkxIOWmeQg8ZAESCIk28BBAw8NAjSI0CD9kqxumQtXacrR1KPZq3Ks6qdpumqSuqYuaWh6HZJ8h926AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t, #"Result (all related combinations)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type text}, {"To", type text}, {"Result (all related combinations)", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Result (all related combinations)", "Index"}),
    #"Filtered Rows" = Table.Distinct(Table.SelectRows(#"Removed Columns", each ([To] <> " "))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each let 
Search = #"Filtered Rows"[From],
Result =  #"Filtered Rows"[To],
Loop = List.Generate(
            ()=>[i=try Result{List.PositionOf(Search,[From])} otherwise "NA",k=List.Combine({{},{i}})],
            each [i]<>"NA",
            each [i=try Result{List.PositionOf(Search,List.Last([k]))} otherwise "NA",k=List.Combine({[k],{i}})],
            each [k]
    ),
    Res = Loop{List.Count(Loop)-1},
   Loop2 = List.Generate(
            ()=>[i=try Search{List.PositionOf(Result,[From])} otherwise "NA",k=List.Combine({{},{i}})],
            each [i]<>"NA",
            each [i=try Search{List.PositionOf(Result,List.Last([k]))} otherwise "NA",k=List.Combine({[k],{i}})],           
           each List.Reverse([k])
    ),
    Res2 = try Loop2{List.Count(Loop2)-1} otherwise {}

    in Text.Combine(List.Combine({Res2,{[From]},Res}),",")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"From", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(Table.SelectColumns(#"Added Custom",{"From", "Custom"}),{{"From", "CAT"}})&Table.RenameColumns(Table.SelectColumns(#"Added Custom",{"To", "Custom"}),{{"To", "CAT"}}),
    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns"),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"From"}, #"Removed Duplicates", {"CAT"}, "Removed Duplicates", JoinKind.LeftOuter),
    #"Expanded Removed Duplicates" = Table.ExpandTableColumn(#"Merged Queries", "Removed Duplicates", {"Custom"}, {"Custom"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Removed Duplicates",{"Result (all related combinations)"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom.1", each if [Custom] is null then [From] else [Custom]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Custom.1", "DesiredResult"}})
in
    #"Renamed Columns1"

[wlknsnBI]

That's awesome, except for the last node. This one seems to be excluded:

[smpa01]

@wlknsnBI  edited