Forum Discussion

Pete230's avatar
Pete230
Helper III
2 years ago

Create a costum column which counts consecutive negative numbers

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)

BC
80
50
-53
-473
-43
10
50


Thanks and regards

Pete

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Pete230
      Helper III
      2 years ago

      Hi Anonymous ; do you have any advice for that issue too?
      That would be very much appreciated 🙂

      Cheers, Pete

  • Pete230's avatar
    Pete230
    Helper III
    2 years ago

    Dear Anonymous ,

     

    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