Count number of zeroes preceding a positive number

[Anonymous]

Hello, I have a data column that looks like this: 0, 0, -1, -2, 0, 0, 1, 2, 0, 1, etc

How do I count the number of zeroes preceding a positive number (in this case, the answer should be 2). Thanks you!

[Anonymous]

Do it in Power Query. Not in DAX.

Best
D

[Greg_Deckler]

You would need an Index column or a date column so that you would know what is "before".

New Column =
  IF([Column] <= 0,
    BLANK(),
    VAR __LastNonZero = MAXX(FILTER('Table',[Index] < EARLIER([Index]) && [Column] <> 0),[Index])
    RETURN [Index] - __LastNonZero - 1
  )

[Anonymous]

@Greg_Deckler

Thanks for your repsonse, but I don't think this is what I needed. I am looking to create the "first change?" column. 

Date	Index	Number	First change?
1/4/2016	1	0	No
1/11/2016	2	1	Yes
1/18/2016	3	1	No
1/25/2016	4	2	No
2/1/2016	5	0	No
4/16/2018	6	0	No
4/30/2018	7	0	No
5/7/2018	8	1	Yes
5/14/2018	9	0	No
5/21/2018	10	0	No
5/28/2018	11	0	No
6/4/2018	12	0	No
6/11/2018	13	0	No
6/18/2018	14	0	No
6/25/2018	15	2	Yes
7/2/2018	16	3	No
7/9/2018	17	3	No
7/16/2018	18	3	No
7/23/2018	19	0	No
7/30/2018	20	0	No
8/6/2018	21	0	No

[Anonymous · Accepted solution]

Paste this into the Advanced Editor in Power Query and see it happening:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc3BDcAgDEPRXXLmQBwodJaI/dcopZV9e4pi/UxzK1ZtlTRs+VFQbQtHnX8XNajJxc2bV1EVhxhiEzub/qbi4xCnqBpUw19bDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", 
        "Condition Met?", 
        (r) => 
            let
                currentNumberGreaterThan0 = r[Number] > 0,
                previousNumberIs0 = 
                    Table.SelectRows(
                        #"Changed Type",
                        each [Index] = r[Index] - 1
                    )[Number]{0}? = 0,
                conditionMet =
                    currentNumberGreaterThan0 and previousNumberIs0,
                output = if conditionMet then "Yes" else "No"
            in
                output
    )
in
    #"Added Custom"

Best

D

[Anonymous]

Unbelievable! Thank you!

[Pragati11]

Hi @Greg_Deckler ,

Interesting solution! 🙂

Thanks,

Pragati