“There’s no unique ID in my Excel file making it hard to use in Power Automate flow, is there an easy way to add such ID?”
When you work with data in rows, let it be SharePoint, Dataverse, or Excel rows, you always need some unique identifier. While Dataverse and SharePoint have such ID by default, Excel does not. And that can be a problem as without ID there’s no way to identify a specific row to select, update, or remove it.
Is there a way to add such unique ID also to Excel rows? Some solution without entering it manually for each new row?
Add a column with a simple formula
One of the functions available in Excel is ROW(): if you use it in a cell it’ll return the row number. That’s the function you can use in your Excel table. Add a new column and set its value to:
=ROW()Since the tables used in Power Automate must have headers, remove the first rows from the numbering.
=ROW()-1
Expand the formula to the all existing rows to assign the unique ID, the new rows added to the table will get the next ID automatically. Just make sure that you don’t rewrite the value in this cell when creating/updating a row!
Once you have the id you can use it to find/update/delete the row.
Summary
Having a unique ID for each Excel row will make your Power Automate life so much easier. And as you can see, it’s very easy to add such ID, all you need is a simple function in one of the columns. Since all data processed by Power Automate must be in a table, you don’t have to worry about updating the column. Just use the function and each new row will continue with the numbering.
Thanks for that top tip! Helped me loads
That’s a nice tip but can you do that from a power automate? aka – it does not have one?
Hello Tim,
if you want something unique from Power Automate you can use the guid() expression, but it’s obviously much longer and harder to read than this column in the Excel file.