Forum Discussion
Deleting First 7 Rows
- Jun 15, 2023
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)
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.
- SergeiBaklanJun 15, 2023MVP
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.
- PhishdawgJun 15, 2023Brass ContributorThe 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- SergeiBaklanJun 15, 2023MVP
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.
- LorenzoJun 16, 2023Silver Contributor
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