Skip to main content

Notifications

Community site session details
Power Automate - Building Flows
Answered

Update multiple rows without unique column and value (and without Office Script)

Like (0) ShareShare
ReportReport
Posted on 7 Jul 2023 03:52:55 by

Hi, I want to update rows in excel based on certain conditions, but my table does not have unique column. I also cannot manually add unique column in the excel file because it will defeat the dynamic purpose. 

 

How can I create the unique column and value solely using Power Automate itself and update multiple rows? I don't have Office Script running in my current version of Excel.

  • Community Power Platform Member Profile Picture
    on 18 Jul 2023 at 06:23:25
    Re: Update multiple rows without unique column and value (and without Office Script)

    Hi Tom,

     

    Is there any way I can make this run faster? Because I deal with large data (almost 2000 records) and it has been running for more than 5 hours to complete the flow. I have tried using filter array and parse json before update row to generate the key value because I only need to update row that Engagement No. contains 98601535 but it doesn't work. 

    Is there any way I could solve this out? Thanks!

  • Community Power Platform Member Profile Picture
    on 10 Jul 2023 at 06:33:36
    Re: Update multiple rows without unique column and value (and without Office Script)

    Appreciate the help @trice602 ! I can't really show how the data looks like but to summarize:

    1. I'm doing a workflow to update rows if a column equals to a certain value. However, my excel file could contain different number of worksheets and records but the header column are always the same.
    2. The data is not formatted as table so I have to create table first for each worksheet. After creating the tables, that's when the updating rows start.

    Here is the screenshot of the detailed workflow for apply to each action for worksheet:

    maryamcs_0-1688970670775.png

     

    Thanks!

  • trice602 Profile Picture
    13,768 Super User 2025 Season 1 on 10 Jul 2023 at 06:24:51
    Re: Update multiple rows without unique column and value (and without Office Script)

    Here's the replay video of my flow:  create keys.mp4

  • trice602 Profile Picture
    13,768 Super User 2025 Season 1 on 10 Jul 2023 at 06:11:13
    Re: Update multiple rows without unique column and value (and without Office Script)

    Sorry to hear that, I will review this week.  Your flow is different than mine, you have an apply to each at the worksheet level and another one nested apply to each at the table level so I would need to know more about those worksheets and tables to understand.  Please send in message if you don't want to show your work in the post. 

     

    Always glad to help! Tom

  • Community Power Platform Member Profile Picture
    on 10 Jul 2023 at 03:57:18
    Re: Update multiple rows without unique column and value (and without Office Script)

    @trice602 since i'm doing everything dynamically, I did it like this:

    maryamcs_0-1688961416471.png

     

  • Community Power Platform Member Profile Picture
    on 10 Jul 2023 at 03:54:08
    Re: Update multiple rows without unique column and value (and without Office Script)

    Hi again @trice602 

     

    It seems like sometimes it works but sometimes it doesn't. There are some records that didn't get updated (for both columns) and it usually happen at last few records. Please refer to the screenshot below.

     

    Screenshot 2023-07-10 114253.png

    I'm using a large data for the screenshot above but it also happens to small data file.

     

    My workflow is like this:

    Screenshot 2023-07-10 114611.png

     

    Screenshot 2023-07-10 114632.png

    Screenshot 2023-07-10 114655.png

    I added compose at last because sometimes it does completely work but it alwats gives error "ActionFailed. An action failed. No dependent actions succeeded."

     

    Perhaps you might help me on this issue? Thanks a lot!

  • trice602 Profile Picture
    13,768 Super User 2025 Season 1 on 10 Jul 2023 at 02:56:24
    Re: Update multiple rows without unique column and value (and without Office Script)

    Always glad to help!  Be sure to tag me in your future posts so I can review and help!  Tom

  • Community Power Platform Member Profile Picture
    on 10 Jul 2023 at 02:43:49
    Re: Update multiple rows without unique column and value (and without Office Script)

    Wow it really works, thank you so much for helping me out @trice602 !

  • Verified answer
    trice602 Profile Picture
    13,768 Super User 2025 Season 1 on 07 Jul 2023 at 20:27:21
    Re: Update multiple rows without unique column and value (and without Office Script)

    Hi @Anonymous ,

     

    Here you go!  Please give me a thumbs up and mark as a solution if this works for you - always glad to help! Tom

     

    So in this case, let's assume there isn't a column with unique values and to do this we need to create a temporary unique column values as we create our key.  Remember, step one is to simply add the key column in a separate flow that you already completed.

     

    Next in my sample data, you can see I don't have a column with distinct values.  I will create a random number as my keyvalue from 11111111 to 99999999.  As we create the keys, I am referencing my description column and making it unique as I go through each row by adding [xxxx] at the end.  This allows me to assign keyvalues to rows that have duplicate description values.

     

    My description concat is:  concat(items('Apply_to_each')?['Description'],' [',rand(1111,9999), ']')

     

    My keyvalue is: int(rand(11111111, 99999999))

     

    Not included here - if you absolutely needed to remove the temporary value in the description, you could loop back through each row and capture the length and only keep the length - 7; to remove _[xxxx] and I would do that in a separate clean up flow.

     

    This was a little bit of a challenge but works.

     

    key2.png

     

    key3.png

    key4.png

     

    key5.png

  • Community Power Platform Member Profile Picture
    on 07 Jul 2023 at 15:27:05
    Re: Update multiple rows without unique column and value (and without Office Script)

    Hi @trice602 , thanks for the help!

     

    I tried using that but it gives out column with empty value. Is there any way I can do to fill the column with key values? 

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

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Building Flows

#1
stampcoin Profile Picture

stampcoin 97

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 82 Super User 2025 Season 1

#3
David_MA Profile Picture

David_MA 48 Super User 2025 Season 1

Overall leaderboard