cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamieF4T
Frequent Visitor

Update row in Excel after listed rows have been filtered.

Hi,

 

I'm trying to update a row in a test spreadsheet after I have applied a couple of filters to the data.

The spreadsheet is a simple table;

Sheet.jpg

 

 

 

 

1)  I have a manual trigger that just collects some text for comment.

2)  I have a List Rows that is pulling in the data from the spreadsheet.

3)  I then apply 2 filters, one to pull out all the items for Duck and the second to check Have Got = No.

Filters.jpg

 

 

 

 

 

 

 

 

 

 

The filters are working correctly and I end up with Output that contains just the one row of data for Item - Duck, Have Got - No

4) This is where I hit the problem, I now try to update the comment on this filtered data with Update Row.

add row.jpg

 

 

 

 

 

 

 

 

 

 

 

 

This update is ignoring the filters and just updating the first item with Duck in it.

 

How do I get it to just update the filtered information?  I thought that the Apply to each step would have made it just write to the data it retrieved from the filters.

 

Thanks

Jamie.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi!

 

If you follow the approach I suggested you would update just the rows from your filtered data. But, if adding an index column cannot be done according to your requirements, I cannot think of any other approach to overcome your challenge.

 

I mean, the original problem is, you have more than one entry in your Excel table with Key Value 'Duck', so the first one is the one updated. Your current design approach is IMHO wrong.

 

Happy flowing!

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

4 REPLIES 4
efialttes
Super User
Super User

Hi!

The update is ignoring the filters since the filters are not the input for the update. 'Update rows' take as the input the excel table you reference inside, there is no way to reference a filtered table.

In my opinion, you need a key column with unique values. Let's assume you add a new column to your excel table, let's call it 'Index', and provide a unique value per row (1, 2, 3...)

Now, you should modify slightly your flow, assign as Key Column 'Index' and assign as 'Key value' the following WDL expression:

item()?['Index']

Please note you will probably need to delete and add 'Update row' first in order to read the new Column structure

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi,

 

Thanks for your reply but it doesn't really do what I was hoping.  This is just a test sheet to prove the process for other things but what I want to be able to do is just amend/add to the data that is returned from a filter array.   

 

The filters are pulling the data down to a single rows worth of information but it seems nuts that once you have that 'Output' you can't then action changes on just the 'Output' data.

duck filter.jpg

 

 

 

 

 

 

I basically just want to add to the "comment": "" listed above.

Hi!

 

If you follow the approach I suggested you would update just the rows from your filtered data. But, if adding an index column cannot be done according to your requirements, I cannot think of any other approach to overcome your challenge.

 

I mean, the original problem is, you have more than one entry in your Excel table with Key Value 'Duck', so the first one is the one updated. Your current design approach is IMHO wrong.

 

Happy flowing!

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

I got there in the end using your idea for a record ID.

Rather than go into lots of detail, I basically added a RecordID to each record using a generated unique string, (based on timestamp).

When I came to updating the rows I could then run filters and from the filters extract the RecordID and then update the row that corresponded to that RecordID.

Thanks for you help, pointed me in a different way of thinking which worked a treat 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,299)