Finding the matched string by rows

Power Query
1

Hi,
I need to find the matching string as the data was collected from students. Each student has selected his group members. Now, it is required to find the distinct groups amongst the rows of the same column. For reference;

image
image308×616 6.41 KB

The issue is that students selected the registration numbers of their group members in different order from drop down list.
Is there any simple way to find the distinct groups from the list?List of Groups.xlsx (11.0 KB)

2

Hi @Tanzeel.

To help us further analyze your current state and visualize your issue, could you please provide:

  • Your work-in-progress PBIX file
  • A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.

Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.

Greg
_eDNA Forum - Format DAX or PQ

3

Hi Greg,

I already attached the excel file and Power BI file.
query-edna.pbix (23.6 KB)

4

@Tanzeel,

Good to see you - it’s been a while!

Check out this video I did a while back. I think it will provide you all the tools/techniques you need to solve this problem.

If you still need assistance, just give a shout.

I hope this is helpful.

  • Brian
5

Hi @Tanzeel,

I’m not really sure how you want to move on from here but if you want to deal with this in Power Query. Give this a go, just paste this into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVY7biUxDLtL6g3g39gytt4yTdog979GgOCRIme81Zsn2TL1o/T19Vbj/ePfe/37+q3r7fsPpO0lvcrrI6ZoO+6MJtIB6cblS7TXSzh4bAxRT9xZeLCKFsIJXFPvxkvYiKt0UQPOUrAVjg2ot5qsDADUoSYrXmrqYoVrk0BcrzGs8GkDSe12OCjOiGlMGOZxCneD1R0qVQOtMfV8odppuBGPuH56vM0sjG2NWLvojkph+cLHVlMdxTkVVmcRqMMdYIadZUpoygoZqIIfdhtVOVgOBg8pXEWlcGXhyTA10jY306qdN3BrLBaRhoxQCM4wDYRh4WObdRbLsQsHxTXzpQEbk6XAsi1bDwA1/bjBR3S6SRkLlq3CIgvBd2Ojq94jYrR02Z+kruxRO4AABXui2WPkMbKdaoEgyCrGgeSxE8mxB9qxGchE5KZgrhUhKz1r39S4TSxGwxmfI4XNcb+twZvz8bR6MFnRw2yyJOww3O3nPlmoiW5SOg+j1nwLzmvS2Cnj2DKLkSg2kuDrSnzKHYsASLPF9BxL83g/yt2Tpc8H+YyDU7VMchwfjyx+zjlVkyXOTSBsCZCa0ACm3CJUyyroBilNkZNVzaUCxq2zcoimddMjXDJxVM0cNdLWUI83B752y87S4Dah6uuOOUzN8ZEFqGrSgUYveVeoeNsBeLBsoSnpoh+/D3mj61py+WCd/47az8NZ218K8bHIandXWNnZK6bfT1fNAMNeaz5m3tlmMpGPbs/YRpK7kKxHYQc4Ac4jUrGU/6BikZo0ni9bCrnxmeO2tVp4WMtZ3sOPpxxVcFkwuOHJVmL6XCNAR36f7JRj1fQ59y0lPZcLqSI7kbu+WcxlmcznWzPpRRZw07MWzdNcZ2QvtwPcy0yacT2uG/WxkA23CvF0NPORWVvtK9cwWTdNz9bLMWb6bPbQQH//AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Mid-Term (4)" = _t]),
    AddCustom = Table.AddColumn(Source, "Custom", each  
        let 
            t= [#"Mid-Term (4)"],
            myFx = Splitter.SplitTextByDelimiter(";"),
            CallFx = List.Sort( myFx(t)),
            myString = Text.Combine( CallFx, ";" )
        in 
            myString, type text
    ),
    GroupRows = Table.Group(AddCustom, {"Custom"}, {{"Row Count", each Table.RowCount(_), Int64.Type}, {"AllRows", each _, type table [#"Mid-Term (4)"=nullable text, Custom=text]}})
in
    GroupRows

.
This query splits your string, sorts the values and puts them back together again - before grouping them. So if you click of to the side in the white space in the final column (next to the text “table”) you’ll see a preview down below.

I hope this is helpful.

6

@Melissa It’s really wonderful. Thank you very much.

7

Thanks @BrianJ,

I will try to learn from it.

Thanks again.

8

just for fun
query-edna.pbix (23.3 KB)