Deletion of rows in multiple sheets

[Anonymous]

I'd like to automate some reports. 

I have numerous excel files into a folder. Each excel files have multiple sheets. 

Ideally : 

- All excel sheets should have the first row and the last row deleted. 

- Then first row set as header 

Could someone please give me some guidance how to create a function which would perform the above on all excel sheets? 

Thank you, 

Lorenna 

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft, 

Thank you very much for the solution ! 

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on test, you could refer to below steps in query editor:

Sample excel files in my folder:

 

1.Get data from one of the excel files(here is A) and delete the first and last row.

2.Modify the code in advanced editor.

Before modify:

Insert this code and modify the source:

(para as text) as table =>
let
Source = Excel.Workbook(File.Contents("C:\Users\danielh\Desktop\Parameter\"&para&".xlsx"), null, true),

After modify:

Here the query becomes a function and you could enter the name of the excel file and all of them will have the first row and the last row deleted.

Regards,

Daniel He