Forum Discussion
Deleting First 7 Rows
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)
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.
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.
- 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 actionMaybe, 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.
- 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
