Forum Discussion
Power Query- how to replace null with previous row value
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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):
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.
Thank you Anonymous that's exactly what I needed. It's running a bit slow but thats due to the volume of my dataset.