Issue with Person-Type Column in SharePoint List Connection to Synapse

Question

Issue with Person-Type Column in SharePoint List Connection to Synapse

Harikrishnan Arayakeel 25

Hi, I attempted to connect a SharePoint list to Synapse, using it as source and sink to new SQL table. I created a SharePoint link service and utilized an OData query in Copy Data. However, the person-type column is only returning data in int32 format. How can I resolve this issue?

I tried the following codes:

1.https://<your-sharepoint-site-url>/_api/web/lists/getbytitle('<sharepoint-list>')/items?$select=Title,New_x0020_Owner/Title&$expand=New_x0020_Owner and

https://<your-sharepoint-site-url>/_api/web/lists/getbytitle('<sharepoint-list>')/items?$select=Title,NewOwner/Title&$expand=NewOwner.

Despite trying both, it is returning all fields (It should return only selected) without the person name. Also made sure SharePoint got the exact column name What is the best way to get the correct data, or is there any other setting outside the query that I need to adjust?

2 answers

Your answer

Answer 1

Chandra Boorla 14,585 Microsoft External Staff Moderator

@Harikrishnan Arayakeel

As I understand that you are encountering an issue where the Person-type column in your SharePoint List is returning an int32 (ID) instead of the person's name when querying it via OData in Synapse. This happens because, by default, SharePoint stores Person-type fields as lookup fields, meaning you need to explicitly expand them to retrieve additional details like Title (name) or Email.

Here are few troubleshooting steps that might help you in resolving the issue:

Ensure Correct OData Query Format

Use the following query in your Copy Data activity:

https://<your-sharepoint-site-url>/_api/web/lists/getbytitle('<sharepoint-list>')/items?$select=Title,New_x0020_Owner/Title,New_x0020_Owner/EMail&$expand=New_x0020_Owner

$select=Title,New_x0020_Owner/Title,New_x0020_Owner/EMail --> Retrieves only the required fields.
$expand=New_x0020_Owner --> Ensures that SharePoint provides full user details instead of just the ID.

Verify the Column Internal Name

If your column has spaces (e.g., New Owner), SharePoint encodes spaces as _x0020_.

You can check the correct internal name in SharePoint by navigating to List Settings --> Column Name --> Inspect URL.

Test API Outside of Synapse (Optional)

Open the above query in Postman or a web browser.

If the correct data is returned in Postman but not in Synapse, the Synapse OData connector may be ignoring $expand.

Workaround - Using an Azure Function or Power Automate

If Synapse does not properly retrieve expanded data, consider using Power Automate or an Azure Function to extract SharePoint data and store it in a staging table before loading it into Synapse.

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

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.

Harikrishnan Arayakeel 25 Reputation points

2025-03-26T09:09:03.7733333+00:00

Thanks Chandra, let me test this.
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-03-26T20:58:38.2166667+00:00

@Harikrishnan Arayakeel

Following up to see if you get a chance to look into the above suggestion? And, if you have any further query do let us know.
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-03-27T19:11:42.73+00:00

@Harikrishnan Arayakeel

Just checking in to see if you get a chance to look into the above suggestion. 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.
Harikrishnan Arayakeel 25 Reputation points

2025-03-28T09:31:39.7+00:00

Hi Chandra,

I've tried getting internal name from SharePoint, but the query is still returning the whole table. I'm not sure if this is an issue with Synapse.

I also tried creating a flow in Power Automate to sink data to Blob Storage, and it works. However, I want to connect it via Synapse. Could you take me through the steps to do that using an Azure Function?
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-03-28T17:24:29.9633333+00:00
@Harikrishnan Arayakeel

As I understand that you're facing issues retrieving Person-type columns from your SharePoint List in Synapse, and your $expand query isn't returning the expected details. Since you've already tried using Power Automate and confirmed that it works, the issue likely lies in how Synapse handles OData queries.

Could you take me through the steps to do that using an Azure Function?

Solution - Use an Azure Function as a Bridge

Instead of calling SharePoint directly from Synapse, you can use an Azure Function to retrieve and format the data correctly before loading it into Synapse. Here’s the step-by-step approach:

Create an Azure Function to Retrieve SharePoint Data -

The function will use the SharePoint REST API with $expand to get both the name and email of the person.

It will format the response into a simple JSON structure.

Deploy the Function & Test It -

After deployment, test the function by calling it in a browser or using Postman.

Connect Synapse to the Azure Function -

Create a REST Linked Service in Synapse and point it to the function’s URL.

Use a Copy Data activity in Synapse to fetch data from the function and store it in an Azure SQL table.

Sample Azure Function Code (Python)

This function extracts Title, NewOwner Name, and Email from SharePoint:

import logging import requests import azure.functions as func import json SHAREPOINT_SITE = "https://your-sharepoint-site-url" LIST_NAME = "YourList" ACCESS_TOKEN = "YOUR_ACCESS_TOKEN" def main(req: func.HttpRequest) -> func.HttpResponse: query_url = f"{SHAREPOINT_SITE}/_api/web/lists/getbytitle('{LIST_NAME}')/items?$select=Title,NewOwner/Title,NewOwner/EMail&$expand=NewOwner" headers = { "Authorization": f"Bearer {ACCESS_TOKEN}", "Accept": "application/json;odata=verbose" } response = requests.get(query_url, headers=headers) if response.status_code == 200: data = response.json() processed_data = [{"Title": item["Title"], "NewOwnerName": item.get("NewOwner", {}).get("Title"), "NewOwnerEmail": item.get("NewOwner", {}).get("EMail")} for item in data["d"]["results"]] return func.HttpResponse(json.dumps(processed_data), mimetype="application/json") else: return func.HttpResponse(f"Error fetching data: {response.text}", status_code=response.status_code)

I hope this information helps.

Thank you.
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-04-01T17:13:44.9633333+00:00

@Harikrishnan Arayakeel

Following up to see if the above suggestion was helpful. 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.
Harikrishnan Arayakeel 25 Reputation points

2025-04-07T08:13:42.8566667+00:00

Hi Chandra,

Apologies for the late response. I'm currently trying to find a training on Microsoft Azure Functions to set this up.
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-04-07T18:56:36.3+00:00
@Harikrishnan Arayakeel

That’s a great idea! Learning Azure Functions will definitely help you build the solution we discussed.

To help you get started, here are some beginner-friendly and official training resources:

Create and deploy Azure Functions – A hands-on tutorial to build and deploy your first function.

Azure Functions Overview – Learn about triggers, bindings, and use cases.

Thank you.
Chandra Boorla 14,585 Reputation points Microsoft External Staff Moderator

2025-04-09T00:18:18.23+00:00

@Harikrishnan Arayakeel

Just checking in to see if the above suggestion helped. 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.

Answer 2

You are using the internal name of the person field (you can find this in list settings → column → URL).

Your OData query uses both $expand and $select correctly.

For a person-type column called New_x0020_Owner, your OData query should look like this:

https://<your-sharepoint-site>/_api/web/lists/getbytitle('YourList')/items?$select=Title,New_x0020_Owner/Title&$expand=New_x0020_Owner

$expand=New_x0020_Owner tells it to get the full object.

$select=Title,New_x0020_Owner/Title tells it to return the sub-property Title from that object (i.e., the person’s name).

Workarounds to help you :

Option 1: Use an Azure Function or Logic App as a Proxy

Create an Azure Function or Logic App to query SharePoint via REST API using HttpClient.

Parse the response and return flattened JSON, e.g., with the user’s actual name.

Use this as your Synapse/ADF source.

Option 2: Use Power Automate to Sync Data

Use Power Automate (Flow) to:

Extract data from SharePoint (including person name).

  Write it to a staging Azure SQL table or blob.
  
     Connect Synapse to that data.

Option 3: Use SharePoint List Export via Graph API

Graph API tends to return richer data models.

You can call /sites/{site-id}/lists/{list-id}/items with expand=fields.

More consistent than OData in some cases, though it takes setup (Azure App Registration, permissions, ....).

Harikrishnan Arayakeel 25 Reputation points

2025-03-26T09:09:36.88+00:00

Thanks Amira, Let me test this 3 options

Share via

Issue with Person-Type Column in SharePoint List Connection to Synapse

2 answers

Your answer