Forum Discussion
Phishdawg
Jun 15, 2023Brass Contributor
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 s...
- 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)
Phishdawg
Jun 15, 2023Brass Contributor
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
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
SergeiBaklan
Jun 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.
- PhishdawgJun 15, 2023Brass ContributorOk, 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