How can I get the previous "non zero" value in power query m?

[pmonte]

Let's imagine a simple table:

Index	City	Step
1	NYC	1
2	NYC	0
3	NYC	0
4	NYC	0
5	LA	5
6	LA	0
7	NYC	7
8	NYC	0

How can I changes zeros in Step to the previous non zero value so to obtain the Goal coloumn?

Index	City	Step	Goal
1	NYC	1	1
2	NYC	0	1
3	NYC	0	1
4	NYC	0	1
5	LA	5	5
6	LA	0	5
7	NYC	7	7
8	NYC	0	7

I tried using an index coloumn and List.Range but it only works with the first row with zero (imagine processing row number #2, I can get the value in Step on the previous row which is 1, great. For row #3 unfortunately the previous value is 0).

[Anonymous · Accepted solution]

Hi @pmonte ,

As the data sample you provided, I'd suggest you replace 0 with null and then use Fill Down to simply change the zero value to non zero value.

The final output is shown below:

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

[amitchandak]

@pmonte , Try a new column like

new column =
var _ind = maxx(filter(Table, [City] = ealier([City]) && [Step] <>0), [Index])
var _1 = maxx(filter(table, [Index] =_ind ), [Step])
return
if(isblank(_1), [step], _1)

[pmonte]

Hello @amitchandak and thanks a lot but this in DAX, I'd need to do this in Query M, any ideas?