Combining Common value

Hi Iamnvt,

if you have many values on one row, split them into pairs and apply the code for relation pairs as suggested earlier.

This code splits many values on a row into pairs. For example: A,B,C => {{A,B}, {B,C}}

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcNJRcFaK1YlWAjF0FFx1FNzAXCDbBcxwAQqCGXApXx0FP5hUlFJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Pairs = Table.AddColumn(
        Source, 
        "Pairs", 
        (row) =>
            let
                lst =Text.Split(row[Column1], ", "),
                lstToPairs = List.Accumulate(
                    lst,
                    [lastElement = null, resultList = {}],
                    (state, current) => 
                        if state[lastElement] = null then
                            [
                                lastElement = current, 
                                resultList = {}
                            ]
                        else
                            [
                                lastElement = current, 
                                resultList = List.Combine(
                                    {
                                        state[resultList], 
                                        {
                                            [
                                                First = state[lastElement], 
                                                Second = current
                                            ]
                                        }
                                    }
                                )
                            ]
                )
            in
                lstToPairs
    ),
    #"Expanded Pairs" = Table.ExpandRecordColumn(Pairs, "Pairs", {"resultList"}, {"Pairs.resultList"}),
    #"Expanded Pairs.resultList" = Table.ExpandListColumn(#"Expanded Pairs", "Pairs.resultList"),
    #"Expanded Pairs.resultList1" = Table.ExpandRecordColumn(#"Expanded Pairs.resultList", "Pairs.resultList", {"First", "Second"})
in
    #"Expanded Pairs.resultList1"

Nolock  this gives the expected result!.

Just for further understanding, I am thinking for solution you gave at the first place, if I repeat the code for the "SomethingInCommon" column, it also gives me the result;

How can I make the code recursive with while loop until no further transformation of the value in the row?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcNJRcNZRcFGK1YlWArHBDLiIi46CK5jhqqPgphQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    // split text into a list of values
    TempValueAsList = Table.AddColumn(Source, "TempValueAsList", each Text.Split([Column1], ", ")),
    // create new a column with all rows' values in common
    SomethingInCommon = Table.AddColumn(
        TempValueAsList, 
        "SomethingInCommon", 
        (curRow) => 
            let
                // get all rows containing any of elements of the current record
                tableWithSameElements = Table.SelectRows(TempValueAsList, (nestedRow) => List.ContainsAny(curRow[TempValueAsList], nestedRow[TempValueAsList])),
                // get the column TempValueAsList
                listToUnion = tableWithSameElements[TempValueAsList],
                // union and sort all elements
                resultList = List.Sort(List.Union(listToUnion)),
                // convert the result list to a text
                resultAsText = Text.Combine(resultList, ",")
            in
                resultAsText
    ),
    // remove temp column
    RemoveTempColumn = Table.RemoveColumns(SomethingInCommon, {"TempValueAsList"}),
    TempValueAsList2 = Table.AddColumn(RemoveTempColumn, "TempValueAsList2", each Text.Split([SomethingInCommon], ",")),
    // create new a column with all rows' values in common
    SomethingInCommon2 = Table.AddColumn(
        TempValueAsList2, 
        "SomethingInCommon2", 
        (curRow) => 
            let
                // get all rows containing any of elements of the current record
                tableWithSameElements = Table.SelectRows(TempValueAsList2, (nestedRow) => List.ContainsAny(curRow[TempValueAsList2], nestedRow[TempValueAsList2])),
                // get the column TempValueAsList
                listToUnion = tableWithSameElements[TempValueAsList2],
                // union and sort all elements
                resultList = List.Sort(List.Union(listToUnion)),
                // convert the result list to a text
                resultAsText = Text.Combine(resultList, ",")
            in
                resultAsText
    ),
    // remove temp column
    RemoveTempColumn2 = Table.RemoveColumns(SomethingInCommon2, {"TempValueAsList2"})
in
    RemoveTempColumn2

s