- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
count only clustered elements
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@lbendlin The idea with GroupKind.Local to group only consecutive rows is very nice and smart, thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you very much!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
10-29-2024 04:00 AM | |||
02-13-2023 12:11 PM | |||
02-01-2022 01:16 AM | |||
09-11-2024 01:00 AM | |||
01-26-2024 10:44 AM |