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!



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
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

New Process Advisor Capabilities carousel.png

Read the blog for the latest news

Read the latest about new experiences and capabilities in the Power Automate product blog.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

AI Builder AMA June 7th carousel (up on May 25th, take down June 8th) (1).png

'Ask Microsoft Anything' about AI Builder!

The AI Builder team invite you to ask questions and provide helpful answers at our next AMA.

Users online (1,467)