Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Rygar
Helper II
Helper II

count only clustered elements

Hello Community!

 

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

12345678910Result
xx x  xxx 5
xxxx xx  x6
  x  x  x 0
 xx  x x  2

Any help is appreciated,

best Michael

3 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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.

View solution in original post

dufoq3
Super User
Super User

Hi @Rygar, another solution:

 

Result

dufoq3_0-1723289577574.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

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

View solution in original post

9 REPLIES 9
p45cal
Resolver II
Resolver II

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

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

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

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

dufoq3
Super User
Super User

Hi @Rygar, another solution:

 

Result

dufoq3_0-1723289577574.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thank you very much!

You're welcome


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

lbendlin
Super User
Super User

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.

Thank you very much!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors