Forum Discussion
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 (there are four As)
2 B-C-B-B-L-K-K-L-E-C-Z-R-S 3 (there are three Bs)
...etc
I prefer to do it in M/Power Query as opposed to DAX but if DAX is the only way to do so, that will have to suffice. Any help or suggestion is appreciated.
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 MaxA showcase of powerful Excel worksheet formula. As I'm using Excel2013, the "old school" funcs seem a bit verbose, but still handy as always.
2 Replies
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
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 MaxA showcase of powerful Excel worksheet formula. As I'm using Excel2013, the "old school" funcs seem a bit verbose, but still handy as always.
