count only clustered elements

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSAWMFJBrGRsjF6kSjiFSgqULoAqlUwGEeKhumEtMMdB2xsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t, #"10" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Row", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Row"}, "Column", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Column", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Clustered", (k)=> if Table.RowCount(Table.SelectRows(#"Changed Type1",each [Row]=k[Row] and [Value]="x" and k[Value]="x" and ( [Column]=k[Column]-1 or [Column]=k[Column]+1))) >0 then 1 else 0,Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Row"}, {{"Result", each List.Sum([Clustered]), type number}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Hi Rygar, another solution:

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSAWMFJBrGRsjF6kSjiFSgqULoAqlUwGEeKhumEtMMdB2xsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t, #"10" = _t]),
    Ad_Result = Table.AddColumn(Source, "Result", each 
        [ a = List.Transform(Record.ToList(_), (x)=> if Text.Trim(x) = "x" then 1 else 0),
          b = List.Buffer({0} & List.PositionOf(a, 0, Occurrence.All)),
          c = List.Transform({0..List.Count(b)-1}, (x)=> try List.Range(a, if b{x}=0 then b{x} else b{x}+1, b{x+1} - b{x}- (if b{x}=0 then 0 else 1) ) otherwise {}),
          d = List.Sum(List.Combine(List.Select(c, (x)=> List.Sum(x) > 1))) ?? 0
        ][d], Int64.Type)
in
    Ad_Result

p45cal challenge accepted 🙂

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSAWMFJBrGRsjF6kSjiFSgqULoAqlUwGEeKhumEtMMdB2xsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t, #"7" = _t, #"8" = _t, #"9" = _t, #"10" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Row", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Row"}, "Column", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Row", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Value] = "x") and ([Count] <> 1)),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Row"}, {{"Result", each List.Sum([Count]), type number}})
in
    #"Grouped Rows1"