Power Platform Community Forum Thread Details

Each month I want to export a number from an exel sheet to a Sharepoint column and accumulate the total each month. So Month one I want to export 10, then next month is 7 meaning the column will show a 17. So on and so forth each month untill the value reaches another value in column 2, in this example lets say 20. Month 3 we add another 7 and that brings the total to 24, exceeding column 2 value of 20. This will trigger the flow to reset the value to zero.

I have an excel summing the values each month and a totals column for historical data. I want to keep this data so I dont want to erase it, so the accumluation and erasing has to happen in Sharepoint.

Im not quite sure how to proceed, Im open to running it using excel, sharepoint etc just need help to understand the best way to approach.

Categories:

General topics

I am not sure how to do what you want. But since this is several days old and nobody has replied, I asked Copilot for a suggestion. This is what it had to offer. If you try this approach and then run into specific issues, you'll be more likely to get help when you are having trouble with a specific action in a flow that has already been created and you've attempted to work through the solution.

To achieve this using Power Automate, you can set up a flow that reads the monthly value from your Excel sheet, updates the SharePoint column with the accumulated total, and resets the value when it exceeds a specified threshold. Here’s a step-by-step approach:

### Step-by-Step Guide

1. **Prepare Your Excel Sheet**:
- Ensure your Excel sheet is stored in a location accessible by Power Automate (e.g., OneDrive or SharePoint).
- Identify the cell or range where the monthly values are stored.

2. **Set Up Your SharePoint List**:
- Create a SharePoint list with at least two columns: `AccumulatedTotal` and `Threshold`.
- Initialize the `AccumulatedTotal` column to 0 and set the `Threshold` column to your desired value (e.g., 20).

3. **Create a Power Automate Flow**:
- **Trigger**: Use a **Recurrence** trigger to run the flow monthly.
- **Get Excel Data**: Use the **List rows present in a table** action to read the monthly value from your Excel sheet.
- **Get SharePoint Item**: Use the **Get items** action to retrieve the current `AccumulatedTotal` and `Threshold` from your SharePoint list.
- **Calculate New Total**: Use a **Compose** action to add the monthly value from Excel to the current `AccumulatedTotal`.
- **Condition**: Use a **Condition** action to check if the new total exceeds the `Threshold`.
- **If Yes**:
- Update the `AccumulatedTotal` to 0 using the **Update item** action.
- Optionally, log the reset event or notify someone.
- **If No**:
- Update the `AccumulatedTotal` with the new total using the **Update item** action.

### Example Flow Outline

1. **Trigger**: Recurrence (e.g., every month)
2. **Action**: List rows present in a table (Excel)
3. **Action**: Get items (SharePoint)
4. **Action**: Compose (calculate new total)
```plaintext
add(outputs('Get_items')?['body/value'][0]?['AccumulatedTotal'], outputs('List_rows_present_in_a_table')?['body/value'][0]?['MonthlyValue'])
```
5. **Action**: Condition (check if new total > threshold)
- **If Yes**:
- Update item (set `AccumulatedTotal` to 0)
- **If No**:
- Update item (set `AccumulatedTotal` to new total)

### Additional Tips

- **Logging**: Consider adding a logging mechanism to keep track of each month's values and resets.
- **Notifications**: You can add email notifications to alert relevant stakeholders when the threshold is exceeded and the total is reset.

This approach ensures that your historical data in Excel remains intact while the accumulation and resetting logic is handled in SharePoint. If you need more detailed steps or run into any issues, feel free to ask! 😊