How to update multiple rows using update a row excel

[kiritisaranam]

I have a excel structure as below :

Engagement name	Project Name	Comments
E1	P1
E1	P2
E1	P3

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.

[Jcook]

Hello @kiritisaranam 

Could you share a screenshot of your flow

[kiritisaranam]

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.

[Jcook]

@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

[kiritisaranam]

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?

[Jcook]

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.

[kiritisaranam]

@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.

Id	Engagement name	Project name	Comments
1	E1	P1
2	E1	P2
3	E1	P3
4	E2	P1
5	E2	P2
6	E3	P4
7	E3	P3

[Jcook · Accepted solution]

@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:

{
    "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"
        ]
    }
}

[kiritisaranam]

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? 

Id	Engagement name	Project name
1	E1	P1
2	E1	P2
3	E1	P1

[Jcook]

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 

[kiritisaranam]

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.

[Jcook]

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 

[kiritisaranam]

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.

[Jcook]

@kiritisaranam 

That is awesome.

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

[takolota]

See a Batch Update Excel template using office scripts here:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Excel/td-p/1624706

It’s already optimized for performance, error handling, & ease of use.