Azure Data Factory Lookup filter query for Sharepoint Online List

Question

I would like to filter the Sharepoint Online List based on the Modified date, to get only the last modified records. I am using the ADF Lookup component.

I was trying different queries, however non of them work and I am getting all elements in the list (or an error).

Modified ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:MM:ssZ')}'
Modified ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}'
Modified ge DateTime'@{addDays(utcNow(),-1)}'
$filter=Modified ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:MM:ssZ')}'

ADF code:

"typeProperties": {
                "source": {
                    "type": "SharePointOnlineListSource",
                    "query": {
                        "value": "Modified ge '@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}'",
                        "type": "Expression"
                    },
                    "httpRequestTimeout": "00:05:00"
                },
                "dataset": {
                    "referenceName": "sp_dataset",
                    "type": "DatasetReference",
                    "parameters": {
                        "list_name": "sp_list"
                    }
                },
                "firstRowOnly": false
            }

How it's possible to filter the Sharepoint List from the ADF?

Answer 1

Hi @Filip Lisiecki ,

Thanks for using Microsoft Q&A !!

You need to use $filter in the query to filter the records out of the list based on your condition. Also, you need to pass filter as a text, which you can achieve by using @concat in the dynamic expression. So, you need to create filter like below -
@concat('$filter=Modified ge datetime''',formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd'),'''')

I have tried this in my environment and it worked as expected and fetched only records satisfying the filter condition. Please find below the gif for your reference.

Please let me know if you have any questions.

Thanks
Saurabh

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.