Power Platform Community Forum Thread Details

Hi, I'm trying to update multiple rows in an Excel Online table using Power Apps, but I'm facing issues where only the first record gets updated, and the date filters seem to be ignored.

Objective:

Filter Data: Collect records from the Capacity_dataset where:
- I compare it against two selected values (DateFrom.Selected.Value and DateTo.Selected.Value).
Further Filter: Filter the results by the selected Operation.
Final Collection: Create a collection FilteredTableFinal containing records from the original Capacity_dataset where the Entry_ID matches the filtered results.
Update Excel: Use a ForAll loop with Patch to update the OEE_Adjusted field for all matching rows in the Capacity_dataset.

Code I'm Using:

// Step 1: Filter and Collect Data
ClearCollect(
    FilteredTable,
    Filter(
        Capacity_dataset,
        Value(Year_Month) >= Value(DateFrom.Selected.Value) &&
        Value(Year_Month) <= Value(DateTo.Selected.Value)
    )
);

ClearCollect(
    FilteredTable1,
    Filter(
        FilteredTable,
        Operation = SelectOperation.Selected.Value
    )
);

ClearCollect(
    FilteredTableFinal,
    Filter(
        Capacity_dataset,
        Entry_ID in FilteredTable1.Entry_ID
    )
);

// Step 2: Update Data Using Patch
ForAll(
    FilteredTableFinal,
    Patch(
        Capacity_dataset,
        ThisRecord,  // Attempting to update the filtered records
        { OEE_Adjusted: Value(TextInput1.Text) }
    )
)

The Issues I'm Facing:

Only the First Record is Updated:
- Despite filtering correctly, only the first available row matching the Operation gets updated.
Date Filters Ignored:
- The update seems to ignore the date range (DateFrom and DateTo) completely.
Wrong Placement of Data:
- The updated value gets placed in the first available row with the selected Operation, not the correctly filtered rows.

What I Need Help With:

Why does the Patch function only update the first record and ignore the filtered data?
How can I ensure all filtered rows are updated correctly in Excel Online using Power Apps?
Is there a known limitation with the Excel connector when using ForAll and Patch together?
Any insights into whether ThisRecord loses connection to the original data source after filtering?

Categories:

Error handling

Hi @MS-05011512-0

Firstly try this - I cannot see your data, so cannot comment on the Filter results (I assume that Year_Month is a date value)

With(
   {
      _List:
      Filter(
         Capacity_dataset,
         Operation = SelectOperation.Selected.Value &&
         Value(Year_Month) >= Value(DateFrom.Selected.Value) &&
         Value(Year_Month) <= Value(DateTo.Selected.Value)
      )
   },
   ForAll(
      _List As _Data,
      Patch(
         Capacity_dataset,
         LookUp(
            Capacity_dataset,
            Entry_ID = _Data.Entry_ID
         ),
         {OEE_Adjusted: Value(TextInput1.Text)}
      )
   )
)

Also note that if your dataset is above your Data Row Limit, Excel filtering can be inconsistent in the returned results.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn Buy me a coffee