TransWikia.com

OData Query for Azure Data Factory SPO List Connector Doesn't Return Expected Results

SharePoint Asked by DimUser on October 25, 2021

I am trying to run an OData query via a SharePoint Online List connector in Azure Data Factory, here is my query:

$select=Id, ProjectId, Project/Id, Project/Title&$expand=Project

"Project" is a lookup column within the list.
When I preview the data in ADF I can see ProjectId and Id, but I cannot see Project/Id or Project/Title.

I am undoubtedly doing something wrong; I think my query is correct, so could this be a limitation of the connector?

Could this be a permissions issue? I am connecting via a service principle key.

Is the $expand syntax applicable in this context? The query parses ok and returns results, it just doesn’t return all the results I expect.

I have been looking at multiple MS docs to try and find the answer, and have been questioning the syntax for days. This is the first time I have used this connector, so am unsure what the problem could be.

I have three main questions:

  1. Is this the best method for this type of query? Should I be using a method such as the following
  2. Is the query correct, and if so why does it not return the results I would expect?
  3. What is the best way to overcome this problem?

Thanks in advance for any help/advice that can be offered on this.

One Answer

I've been looking into this also, with the Odata connector of ADF. My conclusion is that ADF does not support $expand and silenty ignores the parameter.

I have found some posts from MS ADF employees, and they don't seem to understand the issue.

My advise is to test the call in Postman, see first if the syntax is correct and the respons is in order. The debugging capabilities on ADF are 'limited'.

Answered by Johannes Vink on October 25, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP