cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kiritisaranam
Microsoft
Microsoft

How to update multiple rows using update a row excel

I have a excel structure as below :

Engagement nameProject NameComments
E1P1 
E1P2 
E1P3 

 

I have a power app which triggers the flow which sends Engagement name and Comments. I am reading the rows using List rows present in a table and using a filter array to which filters the rows based on the Engagement name from the power app. When I use update a row action inside an apply to each loop it is updating only line 1 all the three times. Can any one help me how to update same comments for all the rows which matches the engagement name? i.e., the comments which I send should be updated for all the lines which have E1.

1 ACCEPTED SOLUTION

Accepted Solutions

@kiritisaranam 

That is fine though, as in my example we are filtering the excel sheet first based on engagement. So in theory the rows we are updating do have a unique value of project name.

See below:

pic1.pngpic2.pngpic3.png

 
 

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Engagement name": {
                "type": "string"
            },
            "Project Name": {
                "type": "string"
            },
            "Comments": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "Engagement name",
            "Project Name",
            "Comments"
        ]
    }
}

 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

13 REPLIES 13
Jcook
Super User III
Super User III

Hello @kiritisaranam 

 

Could you share a screenshot of your flow


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Hi @Jcook , Below attached one is the flow I am using. After filtering the rows using Filter Array, I couldn't update the rows I filtered. But the Apply each loop is iterating as many times as the number of rows with the engagement name. Any help on how to update all the rows filtered in the body would be appreciated.

 

Flow.png

@kiritisaranam 

 

I replicated your Flow, and have found an error. For the "Key column" you need to have a unique value for the Key Column


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Background:

I am feeding the same excel as a data source to a Power BI integrated with a Power App. The Power BI shows only one row per Engagement even it has multiple lines with same name. When the user fill in a form in the power app and submits it, I am sending only the engagement name and details filled in by the user. Hence in the flow I want the values sent by the user to be filled all the rows with this engagement name. The only unique column I have is RowId in Excel.

 

@Jcook  If I use RowId as a Key Column, where  and how should I specify the update a row function to update the three rows? Can you explain in detail?

Hey @kiritisaranam 

 

I found a way around this maybe..

 

Is the project name unique across engagements?

If so you can use that as a key column.

Jcook_0-1596815638726.png

 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





@Jcook The project name also is not an unique column. The only unique column in the excel is the Row number. The structure is something like below with around 3000 engagements consisting 8000 projects. The project name may be unique in the engagement but may repeat for other engagement. It is something like below.

IdEngagement nameProject nameComments
1E1P1 
2E1P2 
3E1P3 
4E2P1 
5E2P2 
6E3P4 
7E3P3 

 

@kiritisaranam 

That is fine though, as in my example we are filtering the excel sheet first based on engagement. So in theory the rows we are updating do have a unique value of project name.

See below:

pic1.pngpic2.pngpic3.png

 
 

 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Engagement name": {
                "type": "string"
            },
            "Project Name": {
                "type": "string"
            },
            "Comments": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "Engagement name",
            "Project Name",
            "Comments"
        ]
    }
}

 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

I have tried this solution @Jcook . It worked for 70% of the data but there are some engagements where the data is like in below table. So I need some solution which does not take project name as unique. As you mentioned in your example, is it possible to get the  id's of the rows which matches with the engagement name and then update all the filtered row id's with comments? 

 

IdEngagement nameProject name
1E1P1
2E1P2
3E1P1

Hello @kiritisaranam 

 

Unfortunately, That is not possible in Power Automate today.

 

I have tried with internalID and it fails. You might need to have a auto number in the excel as an ID column 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





I do have a Id column in the excel added using =Row() function. Could you please help me updating the filtered rows of the filtered array using this RowId column.

Hi @kiritisaranam 

 

Yes of course.

 

You can follow my example from above,

For the parse JSON action. You can put {} as the schema than run the flow and copy the input in the Parse JSON action, than use the copied value to Generate sample schema

Than

just change the key column value to your RowID and than pass in the value of the ID from the Parse JSON action

 

if you would like me to build the flow and show you, please provide a sample of all excel columns 


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Thanks @Jcook  for the help. Your solution worked by using Row Id column. Please find my additional findings below.

Findings :

  1. There is an error in my flow in the apply to each loop. I have used apply to each twice and used excel output to update the rows. I assume that is the reason why it did not work.
  2. I am not sure what is the purpose of using parse JSON action. My Flow worked with only Filter Array action and using Filter Array's  body in apply each step and I have used Row ID as key column.

 

 

 

 

@kiritisaranam 

 

That is awesome.

 

As for the parse json, it’s just a habit of mine.


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,964)