Unanswered

Shift Excel Cells to the right - When specific value found

(0) Share

Report

Posted on by CU25111009-0

Hi,

I am trying to achieve the following: I want Power Automate to check all rows in an Excel list. Whenever there is a ";" in a specific column named 'Ende Date,' the cells should be moved one step to the left. I do not have a unique column ID or anything like that (though I could create one if needed) I am trying to achieve somthing like this:
Example:

My flow looks like this

1- I list the rows and then do apply for each, and then identify each row that has a ";" in "Ende Date" column.

if (apply to each) returns "true", it means this row has ";" and the cells should be shifted to the right:

But it does not work as intended. It gives me an error, and I am not sure what to put in the key column and key value, as I do not have a unique column.

What am I doing wrong here? How should I achieve this without any problems?

thank you very much for your help!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Sort by

rzaneti 4,483 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @CU25111009-0,

The error is happening because your data is not organized in a table format. You have two options: you can either format this range as a table and then execute the steps as you shared, or use Office Scripts to perform operations in Excel ranges not formatted as tables. The first approach can work, if you are able to ensure that your target spreadsheets will be always formatted as table, or if you always get a fixed size for your data range.

Office Scripts is an automation feature from Excel 365, available for most of the enterprise licenses. It is written in TypeScript, and it works similar to VBA. If you would like to move forward with this approach, I can help you to write a script that executes this task: I just need to know in advance if the position of the columns ("Name" in column A, "#" in column B, and so on) is always the same for all of your target files.

For further reference, I'm also sharing some additional content about Office Scripts and Excel integration with Power Automate

- Reading data from Excel Tables in Power Automate (Video): https://youtu.be/UjZlAU1ZGNA

- Inserting and updating data into Excel Tables with Power Automate: https://digitalmill.net/2023/07/24/inserting-and-updating-data-into-excel-tables-with-power-automate/

- Get started with Office Scripts: https://digitalmill.net/2023/06/19/get-started-with-office-scripts/

- Accessing Excel Ranges with Power Automate: https://digitalmill.net/2023/09/01/accessing-excel-ranges-with-power-automate/

- Sending values from Power Automate to Excel with Office Scripts: https://digitalmill.net/2024/01/17/sending-values-from-power-automate-to-excel-with-office-scripts/

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 Accepted Answer.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/
https://www.linkedin.com/in/raphael-haus-zaneti/

Was this reply helpful? Yes No
CU25111009-0 4 on at

Like (0)

Report
Copy link

Link copied!

Thank you Rzaneti,

Unfortunately, I do not have a license to use Microsoft Script. I initially did this with a macro, but later discovered that I cannot use macro-enabled Excel files in Power Automate, so I’m unsure what to do next. The only option seems to be to achieve this with Power Automate. I actually do have the table in the correct format (the screenshot I provided was only to clarify what I want to achieve).

Now, my issue is that I don’t know what to write in the Key column and Key Value, as I don’t have unique values in the table. Can you help me achieve this with Power Automate? What is wrong with my flow, and what should I provide?

Was this reply helpful? Yes No
rzaneti 4,483 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @CU25111009-0,

Just to clarify: there not a separated license for Office Scripts, it's actually available to most generic Microsoft 365 licenses in enterprise level. Here is the list of licenses that support it:

As an alternative, it is also possible to achieve this result with an HTTP request to Graph API. I just ran a quick test from my end, and it is possible to update table records by its index (row number) instead of using a key column/value. This is a more complex approach to perform the task, and it would require you to set up an App Registration in Azure Portal. Another downside of this approach is that it requires a Premium connector, so you cannot execute it with a Power Automate Free license.

I will be happy in providing you with a step-by-step on how to set up the Azure App Registration and integrate the HTTP request into your flow, but I will need a few days for writing it. Before I start, please confirm if these downsides from the HTTP approach (App Registration in Azure Portal + proper Power Automate license) are not a problem for you.

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Accepted Answer.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/
https://www.linkedin.com/in/raphael-haus-zaneti/

Was this reply helpful? Yes No