Share via

Most efficient way to automate the copying of new entries from one Excel file to another

Anonymous
2024-03-07T19:18:35+00:00

I have two Excel files in Office 365 hosted on Teams sites.

The first is the Complaint Submissions file, which shows responses based on List submissions. There are several column in this file, but the vital one is "ID", which is a number in General format.

The second is the Complaint Analysis file, which reorders several of the columns in the "Complaint Submissions" file with similar, but not identical, names. The vital column in this file is "Ref", which is a number in Number format.

I would like to run a daily recurring automate function that copies all rows in the Complaint Submissions file that have an "ID" greater than the largest "Ref" and pastes them at the top of the Complaint Analysis file.

Unfortunately I have stumbled several times in my attempts. At this point I have run into a wall when trying to use the Compose function to change strings to integers in order to use a greater than function with a true/false function.

For unknown reasons I am unable to add photos or screenshots, so I'll do my best to recreate the flow below.

All steps within the "For Each" connector are in bold, explanatory formulas are in italics.

All steps are linear with no parallel branches.

Recurrence -> List rows present in a table (Complaint Analysis) -> List rows present in a table (Complaint Submissions -> Compose (ID_Input) **-> Compose (**ID_Output int(outputs('Compose_-_ID_Input')) **) -> Compose (Ref_Input) -> Compose (**Ref_Output int(outputs('Compose_-_Ref_Input')) **) -> Condition (**ID Output is greater than **** max(outputs('Compose_-_Ref_Output')) ) -> True -> Add a Row into Complaint Analysis Table

Microsoft Teams | Microsoft Teams for business | Files | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-03-07T23:22:00+00:00

    Hello Christopher,

    I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

    To automate the copying of new entries from one Excel file to another in Office 365 hosted on Teams sites, you can use Microsoft Power Automate (formerly known as Microsoft Flow) for this process. Here’s a simplified approach:

    1. Trigger: Use the **Recurrence** trigger to run your flow daily.
    2. Get Data from Complaint Analysis:
      • Use the **List rows present in a table** action to get data from the Complaint Analysis file. Specify the table.
      • Use an expression to find the maximum "Ref" value. You might need a loop to iterate through the rows to find this or use an aggregation function if available.
    3. Get Data from Complaint Submissions:
      • Use another **List rows present in a table** action for the Complaint Submissions file.
    4. Filter New Entries:
      • Instead of using Compose actions for conversion, directly use the expression in the **Condition** to compare ID and Ref. For converting string to integer, you can use the int() function within the Condition itself if necessary.
      • The condition should check if the "ID" from Complaint Submissions is greater than the maximum "Ref" found in step 2.
    5. Add New Entries:
      • For rows that meet the condition (True branch), use the **Add a row into a table** action to insert the row into the Complaint Analysis file.

    Your flow structure would look something like this:

    • Recurrence
    • List rows present in a table (Complaint Analysis) -> Find max Ref
    • List rows present in a table (Complaint Submissions)
    • **For each row in Complaint Submissions**:
      • **Condition**: Check if "ID" > max "Ref" (you might need to handle data conversion here directly in the condition)
        • **True**: Add a row into Complaint Analysis Table

    Make sure to adjust expressions for data conversion directly in the Condition or Filter array step if Power Automate supports this operation for your data types. This avoids the complexity of multiple compose actions for conversion. If you face limitations with direct conversions or comparisons in Power Automate, consider preprocessing steps in your Excel files or using auxiliary columns in your Excel sheets to ensure data is in the correct format for comparison.

    I hope this helps.

    Best Regards, Ibhadighi

    Was this answer helpful?

    0 comments