Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

Update Row action issue

Like (1) ShareShare
ReportReport
Posted on 5 Sep 2024 22:45:56 by 30

Hi Everyone,

I'm facing an issue with my Power Automate flow where I'm trying to update rows in a destination Excel file based on a unique column called "Number". The "Number" column exists in my destination Excel table and is used as a unique identifier for updating rows. However, I am unable to access the "Number" column dynamically in the "Update a row" action. Here's a summary of my scenario:

  1. Source File: I have a source Excel file from which I need to copy data (specifically the Name column).
  2. Destination File: I have a destination Excel file that contains a unique identifier column called Number
  3. Steps in My Flow:
    • I use the "Find files in folder by path" action to dynamically retrieve both the source and destination files.
    • I use the "List rows present in a table" action to get the rows from the destination file.
    • I have an Apply to each loop where I want to match the Number column value to update the row in the destination Excel file.
  4. Problem: In the "Update a row" action, I cannot dynamically select or reference the Number column value from the List rows present in a table action. The Number column doesn’t appear in the dynamic content pane.

What I've Tried:

  • Using dynamic content, but the Number column is not listed.
  • Attempted to manually reference the Number column using expressions like items('Apply_to_each')?['Number'], but this didn't work either

Has anyone else encountered this issue or has advice on how to dynamically access a column that doesn’t show up in the dynamic content?

Any help would be appreciated!

  • Suggested answer
    rzaneti Profile Picture
    rzaneti 3,297 on 12 Sep 2024 at 23:24:47
    Update Row action issue
    Hi @T24k,
     
    Sorry for the late response. The error is happening because you are passing the Name dynamic content to the Provide the item properties input, where an object is expected. The correct syntax will look like this:
     
     
     
    You must set the column to be updated between double quotes, add a colon, and then set its value (as you are working with string values, it must be encapsulated between double quotes as well). Then you just put everything between curly brackets. 
     
    For your specific use case, your input must look like below, where the column name must be set as Name (marked in red), but replacing the value dynamic content (marked in yellow) for the Name dynamic content (just like you already added to the input):
     
     
    Let me know if it works for you or if you need any additional help!
    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Solved.
    If this answer helps you in any way, please give it a like.
     
  • T24k Profile Picture
    T24k 30 on 11 Sep 2024 at 20:28:06
    Update Row action issue
    Hi @rzanati, thank you for your response. Following your instructions, instead of looping directly in the compose action, I provided the file ID, and it is now correctly pulling the file. However, when attempting to update a row, I am encountering an error that says, "The request entity's media type 'text/plain' is not supported for this resource." The output from the "List rows present in a table" action is coming through correctly when I select the files dynamically, as per your guidance.
     
    In this flow for key value i gave item()?['No'] and for provide the item properties i gave item()?['Name']. I am not sure what i am missing. Instead of selecting the file dynamically, if I use the file path in the "List rows present in a table" action, I am able to successfully update the row in the destination file. kindly help! Thanks in advance.
  • Suggested answer
    rzaneti Profile Picture
    rzaneti 3,297 on 10 Sep 2024 at 23:38:53
    Update Row action issue
    Hi,
     
    It looks like your flow need some changes. First, to let your flow more readable and easy to maintain, I recommend you to replace all existing Apply to each for an expression reference to your file. I know that these loops are automatically generated by Power Automate when you select the dynamic content from your Find files in a folder by path action, and it happens because your output is technically an array (a list of files), even if it contains only one file :)
     
    You must transform your Apply to each loops into this:
     
     
    For that, you can use the expression outputs('Find_files_in_folder_by_path')?['body'][0]['Id'], which will reference the Id property from your source file. For the Id of the destination file, you must use outputs('Find_files_in_folder_by_path_2')?['body'][0]['Id']. You can just paste these expressions where you are currently allocating the dynamic content from your "Id" property, in the Excel actions.
     
    After that, add an Apply to each to loop the value property from your source file (List rows in a table action). Inside this Apply to each, allocate your Update a row action, populate it (here using the expression to reference the Id of your destination file), and set your Key value as this expression: item()['No'], similarly to what I did in my previous message.
     
    Have in mind that you won't be able to access the No dynamic content from your List rows in a table action, as you are setting the file dynamically and, because of that, Power Automate don't know your table structure. However, using the expression mentioned above will bring you the same result.
     
    If it still doesn't work or if the instructions are not clear, please share an image of your whole flow (so we can see clearly if your loops are nested), and I will be happy to provide you with some screens as example. 
     
    Also, I'm sharing some blog posts that I wrote about the topics discussed here, that can be helpful for providing you more details about some parts of the instructions:
     
     
    Let me know if it works for you or if you need any additional help!
    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Solved.
    If this answer helps you in any way, please give it a like.
     
  • T24k Profile Picture
    T24k 30 on 10 Sep 2024 at 22:00:21
    Update Row action issue
    Hi @rzaneti, Thank you for your response. I am trying to copy Name column data from source to destination excel  and trying to read the files dynamically. "No" is the unique id column in the destination excel. 
    Source:                                                                                                              
                                                                                                                               
    Destination: 
      My workflow looks like this. 
     
     
     
     
    List rows present in a table shows the value from the source excel. But in update a row action key value is not coming dynamically from destination excel column. please help! Thanks in advance!
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
  • rzaneti Profile Picture
    rzaneti 3,297 on 05 Sep 2024 at 23:34:16
    Update Row action issue
    Hi,
     
    I did a quick test here in a flow with a similar scenario and it's working fine. This is the table:
     
    This is the flow:
     
     
    And this is the result in the table after running it:
     
     
    Could you please confirm if your flow looks like this as well? In any case, can you share an image from your flow design, and the raw outputs from your List rows present in a table action? If you don't know how to access the raw outputs, refer to the steps in this blog
     
     
    Let me know if it works for you or if you need any additional help!
    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Solved.
    If this answer helps you in any way, please give it a like.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

End of Year Newsletter…

End of Year Community Newsletter…

Tuesday Tip #12 Start your Super User…

Welcome to a brand new series, Tuesday Tips…

Tuesday Tip #11 New Opportunities…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 144,654

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,440

Leaderboard
Loading started