Previous value in script load Excel file

[#1]

Hi all,

I have scenario in which i have to load numbers of phones in script. But format of Excel File is complicated:

Number	Traffic	Limit
9308140233	324	200
	11
		100
	76
9308140555	11	300
	22	300
	44	100
9308140777	32	100
	23	0

So I need in load script fulfill the 'Number' field using Previous  function. The first value of phone number need to be inserted in below empty spaces , untill new number.

I want this result:

Number	Traffic	Limit
9308140233	324	200
9308140233	11
9308140233
9308140233		100
9308140233	76
9308140555	11	300
9308140555	22	300
9308140555	44	100
9308140777	32	100
9308140777	23	0

Thank you

[#2]

so I expect this result:

Number	Traffic	Limit
9308140233	324	200
9308140233	11
9308140233
9308140233		100
9308140233	76
9308140555	11	300
9308140555	22	300
9308140555	44	100
9308140777	32	100
9308140777	23	0

[muniyandi]

Hi Try this code

Temp:

LOAD

    Number,

    Traffic,

    Limit

FROM [lib://Muni/half result.xlsx]

(ooxml, embedded labels, table is Sheet3);

NoConcatenate

NewPeek:

Load RecNo() as No,Number,if(IsNull(Number),Peek(Number1,-1),Number) as Number1,Traffic,

    Limit

Resident Temp;

Drop Table Temp;

[tamilarasu · Accepted solution]

Hello Alexander,

Just play with the Enable Transformation step in the File wizard, you will get the required output.

Data:
LOAD RecNo() as RowNo,
    Number,
    Traffic,
    Limit
FROM
Book11.xlsx
(ooxml, embedded labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null))
));

[#5]

It help ! Mucho gracias !

[#6]

It ' s not working .

I get this :

Number1	Traffic	Limit
9308140233	324	200
9308140233	11
		100
	76
9308140555	11	300
	22	300
	44	100
9308140777	32	100
	23	0