Combining Common value

[Iamnvt]

Hello,

I have a field, which contains several values as below:

A, B

D, E, F

A, C

B, C

D, E, G

How can I group them together into a common group? Result as below:

A, B, C

D, E, F, G

A, B, C

A, B, C

D, E, F, G

@ImkeF  please take a look. I think this has to use a custom loop function, but I dont know how

[Nolock · Accepted solution]

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]

Hi @Iamnvt,

I have a solution for you - the code is commented and contains also some sample data. If you have any questions, don't hesitate to ask 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRRcFKK1YlWctFRcNVRcAOzgYLOYIYTjAGRdVeKjQUA", 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"})
in
    RemoveTempColumn

And a screenshot of the result.

[Iamnvt]

@Nolock  absolutely brilliant!

I have a bit more complex scenarios:

Column1SomethingInCommon

A, B	A,B,C,D,E,F
B, C	A,B,C,D,E,F
C, D	A,B,C,D,E,F
D, E	A,B,C,D,E,F
E, F	A,B,C,D,E,F

it has a bridge between A,B and B,C and C, D --> B,C is the bridge --> result should be A,B,C,D

How can I achieve that? 

Thank you very much for the above solution; it already helped me a lot.

[Nolock]

Hi @Iamnvt,

if I understand you well you would like to find a transitive closure. Am I right?

[Iamnvt]

@Nolock  I am not sure what you meant, but here is the scenarious, and expected result:

Col1                                                Expected Result

A, B	A,B,C,D,E,F
B, C	A,B,C,D,E,F
C, D	A,B,C,D,E,F
D, E	A,B,C,D,E,F
E, F	A,B,C,D,E,F

[ImkeF]

Hi @Iamnvt ,

not sure about the pattern here. How would the desired result for these sample data look like?:

A, B

B, C

C, D

D, E

E, F

M, N

D, Z

[Iamnvt]

@ImkeF  here it is the pattern.

A, B, C	A, B, C, D, E, F, Z
B, C	A, B, C, D, E, F, Z
C, D	A, B, C, D, E, F, Z
D, E	A, B, C, D, E, F, Z
E, F	A, B, C, D, E, F, Z
M, N	M, N
D, Z	A, B, C, D, E, F, Z

[Iamnvt]

@Nolock  this is ok if I have only 2 value in a row.

How about the case I have multiple values in a row? Like:

A, B, C	A, B, C, D, E, F, Z
B, C, E, F	A, B, C, D, E, F, Z
C, D	A, B, C, D, E, F, Z
D, E	A, B, C, D, E, F, Z
E, F	A, B, C, D, E, F, Z
M, N	M, N
D, Z	A, B, C, D, E, F, Z

[Nolock · Accepted solution]

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"

[Iamnvt · Accepted solution]

@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 

[Nolock]

Hi @Iamnvt,

I don't recommend using recursion because it can be very slow. Try to split your triples, quadruples, and so on into a list and then apply the solution for 2 columns. It should be straight forward to place these 2 steps one after another.

[Nolock]

Hi @Iamnvt,

I've written a recursive function for a similar scenario earlier: https://community.powerbi.com/t5/Power-Query/Recursive-query-to-derive-indirect-relationships/td-p/7...

But because of the recursion it is painfully slow. If you have a smaller dataset it will be ok.

[Nolock]

Hi @Iamnvt,

here we go, it was a nice exercise.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJSitWBsIzBLCMgywTMMgayLMAsUyDLDMwyA7LM4SygbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"From", type text}, {"To", type text}}),

    // buffer the source table
    BufferedTable = Table.Buffer(ChangedType),

    // create a distinct list of From values
    FromDistinctList = List.Sort(List.Distinct(Table.Column(BufferedTable, "From"))),

    // get all direct descendants as a record (key-value pairs)
    DirectDescendantsRecord = List.Accumulate(
        FromDistinctList,
        [],
        (state, current) =>
            state & 
            Expression.Evaluate(
                "[" & current & "= Table.Column(Table.SelectRows(BufferedTable, each [From] = current), ""To"")]", 
                [Table.Column = Table.Column, Table.SelectRows = Table.SelectRows, BufferedTable = BufferedTable, current = current]
            )
    ),

    // create a table of distict From values
    TableFromList = Table.RenameColumns(Table.FromList(FromDistinctList), {{"Column1", "From"}}),

    // get recursive all descendants of current value
    AllDescendantRelations = Table.AddColumn(TableFromList, "AllDescendantRelations", each fnTransitiveRelationList({[From]}, DirectDescendantsRecord), type list),

    // get recursively list of all descendants
    fnTransitiveRelationList = (toBeDoneList as list, directDescendantsRecord as record) as list =>
        let
            result = 
                if List.IsEmpty(toBeDoneList) then
                    {}
                else
                    let
                        newToBeDoneList = List.RemoveItems(
                            List.Combine(
                                List.Transform(
                                    toBeDoneList, 
                                    each Record.FieldOrDefault(directDescendantsRecord, _, {})
                                )
                            ),
                            toBeDoneList
                        )
                    in
                        List.Union({toBeDoneList, newToBeDoneList, @fnTransitiveRelationList(newToBeDoneList, directDescendantsRecord)})

        in
            result,
    
    // find also all ancestors
    AllRelations = Table.AddColumn(AllDescendantRelations, "AllRelations", (parent) => 
        List.Union(
            List.Select(
                Table.Column(AllDescendantRelations, "AllDescendantRelations"),
                (child) => List.Contains(child, parent[From])
            )
        )
    ),
    ExtractedValues = Table.TransformColumns(AllRelations, {"AllRelations", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

    // join the source table with new results
    JoinBufferedTableWithResult = Table.NestedJoin(BufferedTable, "From", ExtractedValues, "From", "TempTableToExpand"),
    ExpandResult = Table.ExpandTableColumn(JoinBufferedTableWithResult, "TempTableToExpand", {"AllRelations"})
in
    ExpandResult