One of the most common actions you will use in Microsoft Power Automate Desktop is the Read from Excel worksheet action. To read all the data from a particular worksheet in Excel, we will usually make use of the Get first free column/row from Excel worksheet action. However, at times, we have encountered cases where the first free row and first free column variables obtained are not correct.
In this post, we will go through an example case that returns incorrect first free row and first free column values, and provide you with a solution to overcome the problem.
The following examples will be based on the Excel spreadsheet shown in the image down below:
In the Excel spreadsheet example, it contains 7 columns and a total of 10 rows of data. So in this case we assume that we know the number of columns which are fixed but we are not quite sure how many rows of data it has.
Watch the video below, or scroll past the video for step-by-step text instructions.
Reading the data from Excel
- The first action we will deploy on our flow designer would be the Launch Excel action. From here, we will open the Excel file which contains the data we want to read.
2. Usually we will use the action Get first free column/row from Excel worksheet to find out the number of rows in the data. This will store the first free column inside the variable called %FirstFreeColumn% and store the first free row inside the variable called %FirstFreeRow%.
- If we look back at the Excel file data, we expect that the first free column is actually column 8 and the first free row is actually row 12
3. Next, deploy the Read from Excel worksheet action. Here we will specify to read the values from a range of cells. The Start column and row will be 1. The End column will be the variable %FirstFreeColumn-1% and the End row will be the variable %FirstFreeRow-1%.
Note: Remember to put “-1” at the end of FirstFreeRow/Column as we want to read the last filled row/column only.
Note: An additional thing to know is that because in this case we have a header row, we need to select this option First line of range contains the column name.
The Problem: incorrect first free column and first free row values
After we run the flow, we see in the variables pane that the first free column value is 9 instead of 8 and the first free row returns a value of 1001 instead of 12.
You may be wondering why this happens. If we were to look at the Excel data in *csv format (shown in image down below), the reason why it is reading the wrong values is because the Excel file is not sanitized. You can see all the additional rows of data we have below row 11. That’s the reason why Power Automate Desktop is actually returning these values for the first free column and the first free row.
Solving the issue
To overcome this issue, especially when you know that you have a dirty Excel file, an option you can do is to deploy the Get first free row on column from Excel worksheet action instead of Get first free row/column from Excel worksheet. (at step 2)
In this example we will use the first column, so type “1” in column.
Next, when you look at the Read from Excel worksheet action (step 3), choose the %FirstFreeRowOnColumn% variable. Do remember to add “-1” at the end like this %FirstFreeRowOnColumn-1% because we want to read the last filled data. In this case, we assume that we know how many columns there are so we will just put it as column 7.
When you run this new flow, you can see that now it reads the correct number of rows which is 12.
Conclusion
You may run into certain issues when developing your flows when dealing with an Excel file on Microsoft Power Automate Desktop, as the way you view the data and the way the application views and reads the data may be different. In this situation where the Get first free column/row from Excel worksheet action is not giving you the correct values, replace that with the Get first free row on column action instead.