cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Dual Super User III
Dual Super User III

Re: Update row in Excel after listed rows have been filtered.

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
Highlighted
Dual Super User III
Dual Super User III

Re: Update row in Excel after listed rows have been filtered.

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!



Highlighted
Frequent Visitor

Re: Update row in Excel after listed rows have been filtered.

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.

Dual Super User III
Dual Super User III

Re: Update row in Excel after listed rows have been filtered.

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

Highlighted
Frequent Visitor

Re: Update row in Excel after listed rows have been filtered.

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
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (7,077)