Forum Discussion
Solved
Help - Count number of consecutive non null row
I have a very specific request where I need to calculate the number of consecutive non blank row in a certain way : - If there are consecutive values whithout blank value -> count = 1 - If there a...
xichiza
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1V8HRR0lHySswrTSyqBLLcUpOKoEzfxKLkDCDtWFCUmQPmg0S9SvNSwVQOiOdYml5aXAJkBKcWlKTmJqUWKcXqIJluCJTChhVQMIoWI6gwNi2G2LUYo5mKTYshqhYTHA5DxbGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers"," ","|",Replacer.ReplaceValue,{"Employee ID", "January", "February", "March", "April", "May", "June", "July", "August", "September"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Employee ID"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Employee ID", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] = "1")), #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Employee ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}), Custom1 = #"Grouped Rows1", #"Merged Queries" = Table.NestedJoin(#"Promoted Headers", {"Employee ID"}, Custom1 , {"Employee ID"}, "Custom1", JoinKind.LeftOuter), #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"Count"}, {"Count"}) in #"Expanded Custom1"If you remove the last 3 steps, then you get the following if you need it this way:
Fowmy thanks for your reply .
Unfortunaly, I cant share the data as there are confidentials however please find here a set of data and the results I need ("count" column). Better in Dax but if you have a solution using Power Query I will take it too 🙂
| Employee ID | January | February | March | April | May | June | July | August | September | Count |
| Employee 1 | 1 | 1 | 1 | 1 | 1 | 1 | ||||
| Employee 2 | 1 | 1 | 1 | 1 | 1 | 2 | ||||
| Employee 3 | 1 | 1 | 1 | 1 | 1 | 4 | ||||
| Employee 4 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Fowmy
Super User
Super Userxichiza
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1V8HRR0lHySswrTSyqBLLcUpOKoEzfxKLkDCDtWFCUmQPmg0S9SvNSwVQOiOdYml5aXAJkBKcWlKTmJqUWKcXqIJluCJTChhVQMIoWI6gwNi2G2LUYo5mKTYshqhYTHA5DxbGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers"," ","|",Replacer.ReplaceValue,{"Employee ID", "January", "February", "March", "April", "May", "June", "July", "August", "September"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Employee ID"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Employee ID", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}},GroupKind.Local),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] = "1")),
#"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Employee ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
Custom1 = #"Grouped Rows1",
#"Merged Queries" = Table.NestedJoin(#"Promoted Headers", {"Employee ID"}, Custom1 , {"Employee ID"}, "Custom1", JoinKind.LeftOuter),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Custom1", {"Count"}, {"Count"})
in
#"Expanded Custom1"
If you remove the last 3 steps, then you get the following if you need it this way: