Power Platform Community Forum Thread Details

Scenario: I have Sales Orders and Purchase Orders which we output to C:/ as standard excel files (always the same 11 columns / headers) and any number of rows (~10 - 200). These need modified and uploaded into 1 of 2 other systems in a specified format.

Today, we do this data entry and manipulation manually in an Excel Template that uses a few formulas to lookup some additional information and make some calculations.

1. We copy-paste the original data from the SO or PO into one tab in our Excel Template.

2. If none of the formulas have errors and the lookups are working, copy-paste the calculated table into another tab (the import sheet)

3. Manually enter the date of purchase/sale and some accounting info

4. Save the tab as a new spreadsheet and use it for upload to other systems.

High-Level Goal Solution: Ideally, we want to be able to take the Sales Orders and Purchase Orders, upload / send them somewhere (SharePoint, OneDrive, or any alternate system), and get the upload / import file we need without manual entry and just a little bit of processing time.

What I've Tried:

1. When a File is Uploaded (SharePoint and OneDrive)

2. Create Table (original output file doesn't have an Excel table, just data)

3. List Rows Present in Table

4. For Each Row in Source Table, Add Row into Table of Excel Template

5. Copy Values from New Calculated Table in Excel Template to the Import Tab in Excel Template

6. Create New File from Import Tab

7. Delete all rows from all Tables to empty the Template for use in another Flow.

Limitations / Difficulties I am experiencing:

1. After creating a table, table is not found in subsequent step to List Rows of that table. Seems like a timing issue, but even a 10 minute delay doesn't solve this.

2. When trying to Copy/Move/Delete/Edit Excel Files, error saying it cannot be done due to being locked. This happens when trying to correct the automation per #1 and creating a separate flow to trigger when the file is modified after the table is created. I successfully List Rows of the new table, but cannot do anything to the file like edit it or move it to trigger yet another Flow.

3. If creating new files (Copy File) to work with to avoid the file being locked from another automation, the file is either not found or becomes locked in subsequent steps.

4. If Copy File works (seemingly random due to Flow timing), often times not all the data is carried over to the new file when I'm testing with an event where a file/table is modified as the trigger.

If anyone has similar experiences, Flows, solutions, work-arounds, clarifying questions for me, etc., I'd appreciate any and all assistance/insight as I'm completely stumped. It's ideal if it were a solution with Power Automate, but I am not limiting myself to Power Automate as a solution at this point.

Thanks

Categories:

Building flows