Share via

OData Queries aren't working when using the Lookup action in Azure Data Factory

Calvin Worst 0 Reputation points
2025-06-25T16:41:04.47+00:00

I'm trying to list all the files in a specific folder on a SharePoint site that have value in a column called "FakeModified" that is less than 2025-01-01. I am using the Lookup activity in Azure Data Factory with a SharePointOnlineListResource as the Source dataset. I have also confirmed that the data set can see all the columns I'm looking for. I'm using this query:

FakeModified lt '2025-01-01' AND FakeModified ne null AND startswith(Path,'/sites/TestSite/Shared Documents/Test')

Which outputs:

 {
	"count": 15,
	"value": [
		{
			"ContentTypeID": "0x012000B16D1283924E8449B2AF8F3E4229EAC7",
			"Name": "Immutable",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": null,
			"Id": 78,
			"ContentType": "Folder",
			"Created": "2025-06-05T13:50:20Z",
			"CreatedById": 7,
			"Modified": "2025-06-05T13:50:20Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents",
			"CheckedOutToId": null,
			"VirusStatus": "",
			"IsCurrentVersion": true,
			"Owshiddenversion": 1,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x012000B16D1283924E8449B2AF8F3E4229EAC7",
			"Name": "Test",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": null,
			"Id": 79,
			"ContentType": "Folder",
			"Created": "2025-06-05T14:02:13Z",
			"CreatedById": 7,
			"Modified": "2025-06-05T14:02:13Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents",
			"CheckedOutToId": null,
			"VirusStatus": "",
			"IsCurrentVersion": true,
			"Owshiddenversion": 1,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": null,
			"Id": 141,
			"ContentType": "Document",
			"Created": "2025-06-11T15:50:01Z",
			"CreatedById": 7,
			"Modified": "2025-06-11T15:50:04Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Immutable",
			"CheckedOutToId": null,
			"VirusStatus": "18490",
			"IsCurrentVersion": true,
			"Owshiddenversion": 2,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "ArchivedFileNotice.txt",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2025-03-03",
			"Id": 167,
			"ContentType": "Document",
			"Created": "2025-06-12T07:59:03Z",
			"CreatedById": 7,
			"Modified": "2025-06-12T07:59:03Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Immutable",
			"CheckedOutToId": null,
			"VirusStatus": "119",
			"IsCurrentVersion": true,
			"Owshiddenversion": 1,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2025-03-03",
			"Id": 199,
			"ContentType": "Document",
			"Created": "2025-06-24T14:46:14Z",
			"CreatedById": 7,
			"Modified": "2025-06-24T14:46:15Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test",
			"CheckedOutToId": null,
			"VirusStatus": "18504",
			"IsCurrentVersion": true,
			"Owshiddenversion": 2,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document1.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2020-01-01",
			"Id": 200,
			"ContentType": "Document",
			"Created": "2025-06-25T08:41:09Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T08:41:59Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test",
			"CheckedOutToId": null,
			"VirusStatus": "18507",
			"IsCurrentVersion": true,
			"Owshiddenversion": 4,
			"Version": "3.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document2.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2020-03-20",
			"Id": 201,
			"ContentType": "Document",
			"Created": "2025-06-25T08:41:15Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T08:42:17Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test",
			"CheckedOutToId": null,
			"VirusStatus": "18498",
			"IsCurrentVersion": true,
			"Owshiddenversion": 3,
			"Version": "2.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document3.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2025-03-03",
			"Id": 202,
			"ContentType": "Document",
			"Created": "2025-06-25T08:41:21Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T08:41:23Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test",
			"CheckedOutToId": null,
			"VirusStatus": "18505",
			"IsCurrentVersion": true,
			"Owshiddenversion": 2,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document4.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2020-02-15",
			"Id": 203,
			"ContentType": "Document",
			"Created": "2025-06-25T08:41:25Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T08:42:44Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test",
			"CheckedOutToId": null,
			"VirusStatus": "18501",
			"IsCurrentVersion": true,
			"Owshiddenversion": 4,
			"Version": "3.0"
		},
		{
			"ContentTypeID": "0x012000B16D1283924E8449B2AF8F3E4229EAC7",
			"Name": "SubDir",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": null,
			"Id": 204,
			"ContentType": "Folder",
			"Created": "2025-06-25T09:02:14Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T09:02:14Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test",
			"CheckedOutToId": null,
			"VirusStatus": "",
			"IsCurrentVersion": true,
			"Owshiddenversion": 1,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document1.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2020-01-01",
			"Id": 205,
			"ContentType": "Document",
			"Created": "2025-06-25T09:02:27Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T09:02:27Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test/SubDir",
			"CheckedOutToId": null,
			"VirusStatus": "18507",
			"IsCurrentVersion": true,
			"Owshiddenversion": 0,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2025-03-03",
			"Id": 206,
			"ContentType": "Document",
			"Created": "2025-06-25T09:02:27Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T09:02:27Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test/SubDir",
			"CheckedOutToId": null,
			"VirusStatus": "18504",
			"IsCurrentVersion": true,
			"Owshiddenversion": 0,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document2.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2020-03-20",
			"Id": 207,
			"ContentType": "Document",
			"Created": "2025-06-25T09:02:27Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T09:02:27Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test/SubDir",
			"CheckedOutToId": null,
			"VirusStatus": "18498",
			"IsCurrentVersion": true,
			"Owshiddenversion": 0,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document3.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2025-03-03",
			"Id": 208,
			"ContentType": "Document",
			"Created": "2025-06-25T09:02:27Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T09:02:27Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test/SubDir",
			"CheckedOutToId": null,
			"VirusStatus": "18505",
			"IsCurrentVersion": true,
			"Owshiddenversion": 0,
			"Version": "1.0"
		},
		{
			"ContentTypeID": "0x0101004A3BB6D5723B3A4596688708311F18E5",
			"Name": "Document4.docx",
			"ColorTag": null,
			"ComplianceAssetId": null,
			"Title": null,
			"Description": null,
			"FakeModified": "2020-02-15",
			"Id": 209,
			"ContentType": "Document",
			"Created": "2025-06-25T09:02:27Z",
			"CreatedById": 7,
			"Modified": "2025-06-25T09:02:27Z",
			"ModifiedById": 7,
			"CopySource": null,
			"ApprovalStatus": "0",
			"Path": "/sites/TestSite/Shared Documents/Test/SubDir",
			"CheckedOutToId": null,
			"VirusStatus": "18501",
			"IsCurrentVersion": true,
			"Owshiddenversion": 0,
			"Version": "1.0"
		}
	],
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US 2)",
	"billingReference": {
		"activityType": "PipelineActivity",
		"billableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		],
		"totalBillableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		]
	},
	"durationInQueue": {
		"integrationRuntimeQueue": 0
	}
}
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,624 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Chandra Boorla 14,510 Reputation points Microsoft External Staff Moderator
    2025-06-25T18:09:38.8666667+00:00

    @Calvin Worst

    Thanks for sharing the details, you're definitely on the right track, and it's great that you've already verified your dataset and columns.

    The issue seems to stem from how OData queries are interpreted when used with SharePoint lists via Azure Data Factory (ADF). Based on Microsoft’s SharePoint REST API documentation, here are a few key points and recommendations to help get your filter working:

    Key issues in your current query

    Date Format in OData - In SharePoint OData queries, date values must use the OData datetime literal format. For example:

    FakeModified lt datetime'2025-01-01T00:00:00Z'

    Using single quotes alone (e.g., '2025-01-01') may cause the filter to be ignored or misinterpreted.

    User's image

    Null checks - The condition FakeModified ne null is often unnecessary if you're already filtering with lt datetime'...', since null values naturally don’t satisfy the comparison. Removing it can help avoid query conflicts.

    Using startswith on Path - The startswith() function is only supported on basic text fields. The Path property you're referencing is likely a computed field (FileDirRef) and may not support OData filtering directly, causing the clause to be ignored.

    Recommended approach

    To make this work reliably:

    • Use only the supported server-side filter in your Lookup: FakeModified lt datetime'2025-01-01T00:00:00Z'
    • Do not include the startswith(Path, ...) condition in the query, since it likely won’t be evaluated.
    • If folder-level filtering is required, use a Filter activity after the Lookup to filter Path values in ADF instead.

    This approach ensures SharePoint applies the date filter properly, and ADF can then handle the folder-level filtering in-memory.

    For more details, please refer: Use OData query operations in SharePoint REST requests

    I hope this information helps. Please do let us know if you have any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    As your feedback is valuable and can assist others in the community facing similar issues.

    Thank you.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.