Azure Data Factory Lookup filter query for Sharepoint Online List

Filip Lisiecki 21 Reputation points
2021-08-26T07:29:45.383+00:00

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
    2021-08-26T19:44:06.77+00:00

    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.

    126845-getsharepointlistfiltered.gif
    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful