Forum Discussion

matthew_hampton's avatar
matthew_hampton
Helper I
4 years ago
Solved

Maximum Occurrences Within A String

Hello,   I have a column with the following concatenated value:   Row ID     Value                                            Expected Result 1               A-B-A-D-G-H-A-C-B-G-G-A         4 (t...
Need Help
Show and Tell
Tips and Tricks
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUddJ11HXRddf1ANLOQJ47EDoqxepEKxkB5Z3AYk66PrreQOij6wrkR+kG6QYrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row ID" = _t, Value = _t]),

    Max =
        Table.AddColumn(
            Source,
            "Max Cnt",
            each let
                l = Splitter.SplitTextByDelimiter("-")([Value])
            in
                List.Max(List.Accumulate(List.Distinct(l, Comparer.OrdinalIgnoreCase), {}, (s,c) => s&{List.Count(List.PositionOf(l, c, 2, Comparer.OrdinalIgnoreCase))}))
        )
in
    Max

     

     

    A showcase of powerful Excel worksheet formula. As I'm using Excel2013, the "old school" funcs seem a bit verbose, but still handy as always.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

matthew_hampton Use Text.ToList and then you can filter out the dashes and do a group by with a count, return the value with the highest count. Text.ToList - PowerQuery M | Microsoft Docs

 

In DAX, you can do something similar using text to table calculations where you replace your dashes with pipe characters (|) and do similar kinds of manipulation. See my top comment here for the easy way to do this:

(3) Text to Table - Microsoft Power BI Community