pick cell and repeact it as new dimension

[brunobertels]

Hi community 

I'am facing an issue with an excel file and the format of data in it 

Let's say i have this kind of data in an excel file 

Agent 1005
	Type	Number	Time
	Wait	41	0h21'09
	Pause	74	57h00'02
	Break	15	0h35'06
	Personnelle	16	2h56'02
	E-mails	38	51h59'43
	Learning	1	0h14'22
	meeting	1	0h38'36
	mission	3	0h36'13
Agent 1009
	Type	Number	Time
	Wait	21	1h42'41
	Pause	1	0h00'04
	Break	1	0h00'04
	Personnelle	6	0h24'18
	E-mails	2	0h03'21
	Learning	1	0h11'33
	meeting	3	0h09'24

first column is agent number ( ex agent 1005) 
As you can see :

then some others data as type , number and time 

So in final i would have somethink like below : 

AGENT NUMBER	Type	Number	Time
Agent 1005	Wait	41	0h21'09
Agent 1005	Pause	74	57h00'02
Agent 1005	Break	15	0h35'06
Agent 1005	Personnelle	16	2h56'02
Agent 1005	E-mails	38	51h59'43
Agent 1005	Learning	1	0h14'22
Agent 1005	meeting	1	0h38'36
Agent 1005	mission	3	0h36'13
Agent 1009	Wait	21	1h42'41
Agent 1009	Pause	1	0h00'04
Agent 1009	Break	1	0h00'04
Agent 1009	Personnelle	6	0h24'18
Agent 1009	E-mails	2	0h03'21
Agent 1009	Learning	1	0h11'33
Agent 1009	meeting	3	0h09'24

But not able to achieve this in load script 

Can someone help me with that 

Thanks in advance 

Regards  

[Gysbert_Wassenaar · Accepted solution]

Something like this:

LOAD
If(Len(Trim(@1))>0,@1, peek([AGENT NUMBER])) as [AGENT NUMBER],
If(Len(Trim(@2))>0 AND @21<> 'Type', @2 as Type,
If(Len(Trim(@3))>0 AND @3<> 'Time', @2 as Time
FROM ...excel file...
If(Len(Trim(@4))>0 AND @4<> 'Number', @4 as Number,

[brunobertels]

Hi Gysbert 

Thanks a lot , in works fine and it is very simple in the end 

Bruno