Retrieving 'Person or Group' Fields from SharePoint List in Azure Data Factory or Synapse

Sunny Punalkar 0 Reputation points
2024-11-12T14:14:03.32+00:00

I'm able to retrieve data from a SharePoint list using Azure Data Factory > Copy Data (via the SharePoint Connector), but the list contains 'Person or Group' type fields that return only 'Id' values in the results.

These 'Id' values are not useful, as I cannot expand the connected/hierarchical list to obtain additional details like names and emails, even when using a query in the source tab. How can this issue be resolved?

Is there another method in Azure Data Factory, Synapse, or even Databricks to retrieve SharePoint list data?

The query I'm testing is:

$select=ProjectNumber,ProgramManager/EMail&$expand=ProgramManager&$top=1

The preview data shows:

User's image

Thanks,

Sunny

Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,258 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,961 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,920 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Pinaki Ghatak 5,230 Reputation points Microsoft Employee
    2024-11-13T19:03:37.9266667+00:00

    Hello @Sunny Punalkar To resolve this issue, you can use the SharePoint REST API to retrieve the Person or Group fields from the SharePoint list. You can use the expand query parameter to expand the Person or Group fields and retrieve additional details like names and emails. Here is an example of a REST API call that retrieves the 'ProgramManager' field from a SharePoint list: https:///_api/web/lists/getbytitle('')/items?$select=ProjectNumber,ProgramManager/EMail&$expand=ProgramManager&$top=1

    You can use the 'Web' activity in Azure Data Factory to call the SharePoint REST API and retrieve the data. Alternatively, you can use Azure Functions or Databricks to retrieve the data from the SharePoint list and process it as per your requirements.

    I hope this helps

    0 comments No 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.