Forum Discussion
Delete First Seven Rows of Excel Sheets
I have heen tasked with figuring out how to delete the first seven rows of multiple Excel sheets in the same workbook that updates several times a week.
Scenario: An employee receives the workbook to their email inbox several times a week. The workbook has the same name each time, only the data is different.
Currently, a flow move a copy of the workbook to a SharePoint Library.
The first seven rows of each sheet have data in merged column/cells. I need to use Power Query to delete the seven rows on each sheet so that a predetermined remaining set of columns/rows can be turned into a table.
Those tables will then be accessed by Power Automate and moved to a SharePoint List.
Does anyone have insight/examples of how to best successfully achieve this task?
4 Replies
If you want to use Power Query to remove the first seven rows of different excel sheets with same template,
Better to use the folder option when getting the data, transform the sample file and apply the steps.
I used 'get data' and 'from file', it worked and I was able to transform, close and load the data.
I then built an SP List and then a Flow, which I used to copy the transformed data to the list.
How do I automate all of this?
Edited Reply -
Thank you for your reply.
I found Power Query in Excel.
I was able to transform sheet1, removing the top seven rows and saving a new sheet with only the header and rows I want.
I had to do this in desktop, not online. Am I correct that this functionality is ONLY available in thr desktop version of Excel?
If yes, how is this automated so that it happens on its own every morning, if possible?
