How can I return the last cell in a column from excel file in set variable activity adf?

Question

I have an excel sheet and need to extract the data in the last cell of a column to put into a new column in my sink file. I am using a set variable activity, is there a way to specify and return the last cell in the column? I can only make it work if I want the first row of the specified column

@activity('lookup_activity').output.?

Can anybody help?

Answer 1

Hi @JH-AM ,

Please try the following:

1. Un-select "First Row only" checkbox from the lookup settings
2. As you unselected the option "First Row only", you will get count and value (with all the values in that particular column)
3. in your set variable, do something like *@activity('lookup_activity').output.value[sub(activity('lookup_activity').output.count,1)].date *

Thanks!

{  
    "count": 295,  
    "value": [  
        {  
            "date": "2020-12-03T01:41:51.043Z"  
        },  
        {  
            "date": "2020-08-20T01:35:40.41Z"  
        },  
        ...  
}