Forum Discussion
Deleting First 7 Rows
I get an Excel document provided to me in a SP Library. There is no table in the sheet when received and I don't need the first 7 rows.
Is there a way to right a flow that will delete the first seven rows of an Excel workbook sheet, and the turn the new first 30 rows, and predetermined columns (e.a. - A1:AP30), into a table?
Get & Transform aka Power Query is designed to do this kind of things
- Data tab > Get Data > From File > From Excel Workbook
- Select the workbook > Import
- In the Navitor window select the desired sheet
- At the bottom of the Navigator window > Transform Data (Power Query Editor opens)
- In APPLIED STEPS (on the right) delete any step after Navigation
- On the Home tab > Remove Rows > Remove Top Rows > 7 > OK
- Closed & Load (top left of the menu)
7 Replies
Get & Transform aka Power Query is designed to do this kind of things
- Data tab > Get Data > From File > From Excel Workbook
- Select the workbook > Import
- In the Navitor window select the desired sheet
- At the bottom of the Navigator window > Transform Data (Power Query Editor opens)
- In APPLIED STEPS (on the right) delete any step after Navigation
- On the Home tab > Remove Rows > Remove Top Rows > 7 > OK
- Closed & Load (top left of the menu)
- Ok, I gave that a try.
I did get it to work, thank you.
Two questions -
1. It added columns headers (Column1, Column2, Column3, etc.), as row one with my original workbook column headers on row 2. How do I prevent the default numbered columns from being created?
2. Can this process be automated, or is it a manual process? I'd like to apply this to a folder that is added to the SP Library several times a week - automatically. The file name is the same for each new workbook, except the date at the end of the file name.1. It added columns headers (Column1, Column2, Column3, etc.), as row one with my original workbook column headers on row 2. How do I prevent the default numbered columns from being created?
Assuming I understand (not sure)... You can't prevent this from happening as a Table must have headers and if you don't provide those headers in a way or another, Power Query - as default - name them Column1, Column2...
As I understand your scenario, either before or after removing the top 7 rows you can rename Column1, Column2... as you want. Just double-click a column name and enter the desired name
2. Can this process be automated...
Looks like you're in good hands with SergeiBaklan and I don't have access to Power Automate so won't be able to help with this
