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.
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!
Appreciate the help @trice602 ! I can't really show how the data looks like but to summarize:
Here is the screenshot of the detailed workflow for apply to each action for worksheet:
Thanks!
Here's the replay video of my flow: create keys.mp4
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
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.
I'm using a large data for the screenshot above but it also happens to small data file.
My workflow is like this:
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!
Wow it really works, thank you so much for helping me out @trice602 !
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.
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?
#1
WarrenBelz
146,662
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,004
Most Valuable Professional
Share
Report
All responses (
Answers (
