Create a costum column which counts consecutive negative numbers

[Pete230]

Hi together;

I would like to create a costum column C in power querry which counts the consecutive negative numbers in Column B.
Which forml needs to be put in here:

to achive the following goal (result in column c)

B	C
8	0
5	0
-5	3
-47	3
-4	3
1	0
5	0

Thanks and regards

Pete

[Pete230]

Dear @v-jialongy-msft ,

thanks for the quick reply; unfortunatly i get an error saying can not be converted into a list:

my table and datatypes are the following:

Any idea how to solve that?

Cheers, Pete

[v-jialongy-msft]

Hi @Pete230 

First, add an index column in power query.

Then use the following code to add a custom column:

let
CurrentRow = [Index],
ListNegative = List.LastN(
                List.FirstN(Source[B], CurrentRow+1),
                each Number.FromText(_) < 0
            )
in 
List.Count(ListNegative)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pete230]

Hi @v-jialongy-msft ; do you have any advice for that issue too?
That would be very much appreciated 🙂

Cheers, Pete