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

JH-AM 41 Reputation points
2021-01-29T16:13:20.077+00:00

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?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,732 questions
{count} votes

Accepted answer
  1. Nasreen Akter 10,761 Reputation points
    2021-01-29T16:38:43.4+00:00

    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"  
            },  
            ...  
    }  
    
    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful