Counts if value is not blank and then start again

[Anonymous]

Hi i have a set of data from different dates and for each date there is milliseconds. 

what i am trying to achieve is a cumulative count until my first  blank and then start again from 1

Desired result:

Diff	New Column
21.1	1
21.2	2
21.3	3
Null	Null
25	1
25.2	2
26	3
27	4

[Zubair_Muhammad]

@Anonymous 

Please see attached file's Query Editor for clarity as well

[Anonymous]

Hi, thanks allot, maybe you can also help me crate this column:

Thanks again

Diff	Index	Sprint No
21.1	1	1
21.2	2	1
21.3	3	1
Null	Null	Null
18	1	2
18.1	2	2
19	3	2
20	4	2
Null	Null	Null

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous 

You can use this Custom Column.

See file attached as well

=let myindex=[Index] in
Table.RowCount(Table.SelectRows(#"Added Custom1",each [Custom]=null and [Index]<=myindex))+1

[Anonymous]

marvelous!

[Zubair_Muhammad]

@Anonymous 

Assuming you have an Index Column starting from 0, I believe you can use this Custom Column from Query Editor

Replace AddedIndex with the PreviousStep name in your Query Editor

let myindex=[Index] in
let mytable=Table.SelectRows(AddedIndex,each [Index] < myindex and [Diff]=null)
in
if [Diff] <> null then if Table.IsEmpty(mytable)=false then [Index]-Table.Max(mytable,"Index")[Index] else [Index]+1 else null