Deleting First 7 Rows

[Phishdawg]

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?

[#2 · Accepted solution]

@Phishdawg 

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)

[#3]

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.

[#4]

@Phishdawg 

As variant,

you may create ExcelScript under Automate like

function main(workbook: ExcelScript.Workbook) {

	const sheet = workbook
		.getWorksheets()[0]

	sheet
		.getRange("1:7")
		.delete(ExcelScript.DeleteShiftDirection.up)

	const newTable = workbook
		.addTable( sheet.getRange("A1:M100"), false )

	// clean empty rows
	const values = newTable
		.getRangeBetweenHeaderAndTotal()
		.getValues()
	const rows: number[] = []
	let rowInd: number = 0;
	
	values
		.forEach( v => {
				if ( v.join("") == "") {
					rows.push(rowInd)
				} rowInd++
		} )

	for (let n = rows.length - 1; n > -1; n--) {
		newTable.deleteRowsAt(rows[n]);
	}

}

which removes first 7 rows, on predefined with some gap creates the table and removes empty rows from it.

Next, in Power Automate create the flow, just couple of steps could be enough - it is triggered when any new file is added to the SharePoint folder and run above script on that file

Above is only mock-up, details depend on your environment and logic of the process.

[#5]

The end goal is to continually maintain the content of a SP List.

My thinking -
1. Make the flow a recurring scheduled flow.

2. Add to your example, after the 'Run script', a 'Get items' to retrieve the data from an SP List.

3. Create a 'Condition' that compares the 'Title' of the SP list to the 'EmployeeNo' column of the
new Excel table created by the 'Run script'.

4. Replace the 'Send an email (V2) -
If Condition 'Yes' - Update items action
If Condition 'No' - Create items action

[#6]

@Phishdawg 

Maybe, but I don't think it'll be so easy.

Triggered flow returns you all properties of file, with scheduled flow you have to predefine filename or filter it from the list of all files.

Comparing two arrays in general we shall to iterate both one by one - for each item of first iterate entire second and perform some operation if we met (or don't met ) condition. That's very time consuming operation. As I remember there are some workarounds with filtering, but in any case will have lot of Apply to each.

Send email in the sample was only for testing purposes.

[#7]

Ok, I'll use SharePoint 'When item is created' - pointing at an SP Library specifically created for this/these files ONLY.

That should solve the first problem, yes

[#8]

@Phishdawg 

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