Azure Data Factory Lookup filter query for Sharepoint Online List

Filip Lisiecki 21 Reputation points

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?

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

Accepted answer
  1. Saurabh Sharma 23,761 Reputation points Microsoft Employee

    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.



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

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful