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
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.
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"
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!
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
Thank you very much!
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
23 | |
18 | |
12 |