Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 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
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.
@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.
Hi @Iamnvt,
if I understand you well you would like to find a transitive closure. Am I right?
@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 |
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 |
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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 |
@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 |
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
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.