Help - Count number of consecutive non null row

[xichiza]

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 are consecutive values with 1 blank, it shoudl increment the count -> count = 2

Here is what it looks like :

Basically, I want to be able to count the number of consecutive non blank value in a row.

Hope my request is understable (hard to explain).

Thanks for your replies

[xichiza]

Awesome ! Thanks a lot for your help ! 

What a nice and quick way to do it !

[xichiza]

Ok, I have extract some data and create a Power BI report. Can you please tell me if it is ok for you ?

https://www.transfernow.net/dl/20210927W5Sq0Ohm/iPmgUTcn

Thanks again 

[xichiza]

@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 · Accepted solution]

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

@xichiza 

Can share the data that you used in your visual? you paste it directly with your reply. And, how do you want the result to be shown? Are you fine with Power Query solution as well?