Max frequency contiguous data

[royalc19]

Hi,

I’m trying to get like max frequency contiguous data – or to put it more simply

1
0
1
1
0
1
1
1
1
1
0


Longest streak of 1s = 5

Sounds easy right? In excel I can do it - looks like this for longest streak of a column (column F) where the number is less than 900

=MAX(FREQUENCY(IF(F2:F1048576>900,ROW(F2:F1048576)),IF(F2:F1048576<=900,ROW(F2:F1048576))))

Would appreciate any help,

Thanks

 

[anvg]

Hi
A variant

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Count", each Table.RowCount(_), type number}}, GroupKind.Local),
    #"Max Record" = Table.Max(#"Grouped Rows", "Count")
in
    #"Max Record"

Regards,

 

[royalc19]

Thanks anvg,

Also would I use this in power BI.