Comparing Delimited Unique Text Values In a Column and Posting a Count of matches.

I've demonstrated how you can compare delimited lists like this regardless of order here:
https://community.powerbi.com/t5/Desktop/Match-between-two-columns-in-different-order/m-p/2204459

However, you can probably make your life a lot easier if you split that column by delimiter into rows via the query editor:

With this, you can create a relationship ValidElements[Element] 1--> * Compounds[Element] and analyzing patterns and relationships should be easier.

Hi Alexis,

Hi Bigglerum 

According to your statement, I know you want to count the same values inculding values which are combined in same cell with different sort, like [ Pb;O;H;Ti] and [ H;O;Pb;Ti].

Try my way by M code. Here is my sample.

1. Firstly add an Inedx in this table. 

2. Split by ";" in Rows, sort your new table by [Column1].

3. Group All Rows by [Column1] , add a new Index and Expand Table.

4. Group Values by Code.

 #"Group Value"= Table.Group(#"Expanded Rows", {"Rows.Index","Rows.Value"}, {{"New", each Text.Combine([Column1], "; "), type text}}),

5. Then it's easy to count Rows by same [New].

My M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkiy9rf2sA7JVNJRMlaK1YlWCgYxjcBMD6AcUAFY0hQs4m8NljZXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Column1", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Column1"}, {{"Rows", each _, type table [Column1=nullable text, Value=nullable text, Index=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Index1", "Rows", {"Value", "Index"}, {"Rows.Value", "Rows.Index"}),
    #"Group Value"= Table.Group(#"Expanded Rows", {"Rows.Index","Rows.Value"}, {{"New", each Text.Combine([Column1], "; "), type text}}),
    #"Sorted Rows1" = Table.Sort(#"Group Value",{{"Rows.Index", Order.Ascending}}),
    #"Grouped Rows1" = Table.Group(#"Sorted Rows1", {"New"}, {{"Count", each _, type table [Rows.Index=nullable number, Rows.Value=nullable text, New=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.RowCount([Count])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Rows.Index", "Rows.Value"}, {"Count.Rows.Index", "Count.Rows.Value"}),
    #"Sorted Rows2" = Table.Sort(#"Expanded Count",{{"Count.Rows.Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows2",{"Count.Rows.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Count.Rows.Value", "Value"}})
in
    #"Renamed Columns"

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.