Forum Discussion

matthew_hampton's avatar
matthew_hampton
Icon for Helper I rankHelper 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.

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
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.