by If you’ve used the Office 365 connector for Power Automate, you’ve noticed that many of the actions require you to provide a “Key Column” to do simple actions like updating a row, for example. Many people get confused when they see it since it’s not common in Excel, so today, I want to explain what a “key column” is and why it’s necessary for Excel’s actions to work.
The problem
Tables in Excel can have data in any part of your Excel file. This means that you can even have multiple tables on the same sheet. It’s a wonderful way to see data in Excel, but we’re automating the actions using Power Automate, so we need a way to access the data behind the scenes. How do we identify, without ambiguity, a row of data in a table that we want to get? For example:
In the example above, we have a list of two comments I made. If I want to get the second row, I need a way to tell Power Automate that I wish to the row with the comment “this is a second comment”. Of course, we could use the row system in Excel, but what if we move the table a few rows down? Or if we add a new row in the middle? Or sort the data by “Comment,” for example. In one case, it would be row “2” but in others could be row “1”. We would not have a way to identify the data we want to collect since the rows keep changing for the same data, so here’s where the key column comes into place.
The key column
The “key column” will have unique values that will identify the data in a row, regardless of where the data is in the Excel table. With it, we would always have a way to reference a row (regardless of where I see it) that has, for example, the “Comment” as “this is a second comment. Notice how we think about it is consistent with how it is implemented in Power Automate. We’re asking for a column (“Comment”) with the data (“this is a second comment”) and not asking for row “2”, since we can sort the column in another way and move the data in row “2” to row “10” for example.
Another essential thing to mention is the key column cannot work alone and needs the “key value” to fetch the data. In the example above, the key column could be “Comment”, and the key row could be “this is a second comment”.
So consider the key column as the way to identify the data itself regardless of the position in the table since data can shift quickly between rows.
Things to know
There are some things that you should be aware of.
Should be unique
The elements in the “key column” should always be unique. The reason is simple. How could we identify the data we want if they are not unique? If you don’t provide unique values, then the “key column” would not help retrieve the data.
Ideally should be numeric
… but you can use strings as long as you keep them unique. The idea is always the same. To have a unique field to identify our data. Avoid using dates or other fields since it would make things trickier. Dates are stored in Excel as numbers bud displayed in Power Automate as strings. It’s possible to deal with them, but we would add an unnecessary extra level of complexity.
Use the column only as an identifier
As a best practice, use the “key column” only as an identification column and not for data. Things change, and a column that you think would never have duplicate values could have them in the future. Having a column dedicated only to this purpose would make your life easier in the long run.
You can hide it
If you want, you can hide the column in Excel, which will still work in Power Automate. This is a good strategy when you want the Excel files to be “clean”. Remember that if you add another row to the table, you must ensure that the “key column” is filled in with a unique value.
Final thoughts
I hope I answered this common question, and now you know why you need the “key column” in Power Automate to access and manipulate data in Excel.
Photo by Florian Berger on Unsplash
How do you create a key column?