Power Query- how to replace null with previous row value

[Mpcz]

Hi All,

I would like to ask for your help with understanding how to replace a null value with value of the previous row.

What i would need to happen based on the below example is to fill up row with index number 18 of "Wykonanie" column with the the value +9 of row with index 17. So the result for row 18 would equal to 76,939+9=85,939. Then for 19th 85,939+9 and so on till it hits the row without null. Please ingore row #12 it will be filled up.

Thank you in advance 🙂

[Anonymous]

Hi @Mpcz ,

I suggest you to try this code to create a custom column.

if [Index]>17 then 
Table.Max(
Table.SelectRows(#"Changed Type",(x)=>x[Index]<[Index] and x[Wykonanie]<>null),
"Index")[Wykonanie]
+ 9000 *([Index] - 17)
else [Wykonanie]

Result is as below.

Best Regards,
Rico Zhou

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

[Mpcz]

Thank you for your time and the code you've shared it's quite close to what I need. But is there a chance to modify this part :

+ 9000 *([Index] - 17)

instead of 17 I would need it to use the index of the previous non blank [WYkonanie] row. I think i haven't shared enough data on my screenshot,sorry fot that. The thing is that it would work perfectly if all of the rows after index 17 would be blank but thats not the case with my dataset. Let's say that rows 18-25 are blank and then rows from 26 to 35 are not, so I would need the code to use the Index 35 in the above mentioned part of your code and so on as there would multiple groups of blanks and non blanks values in column [Wykoananie]. 

If that's even possible I woul really apppreciate your help here, and once again thank you for the time you invested so far.

Below a little bit more insight on my dataset (please ignore Filled column i tried to modify your code at my own which hasn't really worked):

[Anonymous · Accepted solution]

Hi @Mpcz ,

I suggest you to try this code.

if [Index]>17 and [Wykonanie] = null then 
let _Wykonanie = 
Table.Max(
Table.SelectRows(#"Changed Type",(x)=>x[Index]<[Index] and x[Wykonanie]<>null),
"Index")[Wykonanie],
_Index = 
Table.Max(
Table.SelectRows(#"Changed Type",(x)=>x[Index]<[Index] and x[Wykonanie]<>null),
"Index")[Index]
in
_Wykonanie + 9000 * ([Index] - _Index)
else [Wykonanie]

_Index part will catch the max index whose data is not blank.

Best Regards,
Rico Zhou

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

[Mpcz]

Thank you @Anonymous that's exactly what I needed. It's running a bit slow but thats due to the volume of my dataset.