count only clustered elements

[Rygar]

Hello Community!

I need to count only elements next to each other an not the single elements:

1	2	3	4	5	6	7	8	9	10	Result
x	x		x			x	x	x		5
x	x	x	x		x	x			x	6
		x			x			x		0
	x	x			x		x			2

Any help is appreciated,

best Michael

[lbendlin · Accepted solution]

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.

[dufoq3 · Accepted solution]

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]

Another

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]),
    Result = Table.AddColumn(Source, "Cluster Count", each [ToTbl = Table.FromList(List.Transform(Record.ToList(_),(a)=> if Text.Trim(a) = "" or a = null then null else 1), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Sum0 = List.Sum(Table.SelectRows(Table.Group(ToTbl, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local), each ([Column1] = 1) and ([Count] <> 1))[Count]),
        Sum = if Sum0 = null then 0 else Sum0][Sum])
in
    Result

[lbendlin · Accepted solution]

@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"

[Rygar]

@lbendlin The idea with GroupKind.Local to group only consecutive rows is very nice and smart, thanks!

[lbendlin]

@p45cal had it first here, but it originated with @ImkeF who also uncovered the secrets of the fifth parameter.

[dufoq3 · Accepted solution]

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

[Rygar]

Thank you very much!

[dufoq3]

You're welcome

[lbendlin · Accepted solution]

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.

[Rygar]

Thank you very much!