Fill missing values with previous non-null values

[a_kosarev]

Hi everyone,

I have an excel table that looks like this: 

Warehouse	Employee
Warehouse №32	12
	16
	18
	5
	11
Warehouse №15	24
	5
	8
	23
	12
	14
	19
	15
Warehouse №11	20
	13
	7
	25
Warehouse №30	16
	13
	12
	3
	21
Warehouse №33	13
	15
	14

What I want to do is fill the missing values in the warehouse column with the closest previous non_null value. 

I've tried the peek() and previous() function, but they don't seem to do the trick. 

What can I do about the problem? 

[anushree1 · Accepted solution]

Please try the below script, also attached the sample for your reference:

Temp:
LOAD
Warehouse,
Employee
FROM [lib://Web_Conn]
(html, utf8, embedded labels, table is @1);
NoConcatenate
Data:
Load
if(len(trim(Warehouse))>0,Warehouse,peek(Warehouse)) as Warehouse,

Employee
Resident Temp;

drop Table Temp;