Forum Discussion

Mpcz's avatar
Mpcz
Frequent Visitor
4 years ago
Solved

Power Query- how to replace null with previous row value

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Mpcz
Frequent Visitor
3 years ago

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