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

Question

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:

Thanks,

Sunny

Answer 1

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