Power Query Problems

How to Fix SharePoint Person Column Showing IDs in Excel Power Query

Natalie Taylor Oct 21, 2026 868 views

Question details

The user needs to display actual user names instead of numerical IDs when connecting a SharePoint List to Excel via Power Query.

How to Fix SharePoint Person Column Showing IDs in Excel Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing data from a SharePoint List containing a Person or Group column into Excel using Power Query.
Observed behavior: The Person or Group columns display numerical user IDs instead of the expected user display names.

Before you start

Verify your SharePoint site permissions and ensure you have access to either Power Automate or Power BI, as these tools are required to intercept and map the raw database IDs to display names.

Solution 1Recommended

Use Power Automate to Retrieve and Map User Names

Connect your SharePoint list to Excel using Power Automate to dynamically search for and return user names instead of IDs.

Because Power Query extracts raw relational data directly from SharePoint, Person/Group columns naturally default to their database lookup ID.

Using Power Automate allows you to intercept this data and replace the lookup ID with the display name before it reaches your final Excel workbook.

Create a Flow in Power Automate

Log in to Microsoft Power Automate and create an automated cloud flow triggered by the creation or modification of items in your SharePoint list.

Add a Search User Action

Insert the 'Get user profile (V2)' or 'Search for users' connector. Pass the dynamic numerical ID from your SharePoint Person column into this action to fetch the corresponding user details.

Map Display Names to Excel

Use the 'Update a row' or 'Add a row into a table' Excel action. In the mapping fields, select the 'Display Name' dynamic content from the previous user profile step to output the actual names into your Excel workbook.

Automation Benefit: Once configured, this flow will automatically translate IDs to names for all future SharePoint list updates without manual intervention.

Solution 2

Resolve User IDs using Power BI Data Modeling

Import your SharePoint data into Power BI to merge the primary list with the hidden SharePoint user profile information.

Free Microsoft Office alternative

Try WPS Office for a Lightweight Excel Alternative

If you are tired of complex Power Query limitations and heavy Microsoft ecosystem dependencies, try WPS Office. It provides a lightweight, highly compatible alternative to Microsoft Excel with an intuitive interface for managing your daily spreadsheet tasks without unexpected formatting hurdles.

1. Download and Install: Visit the official WPS website to download the free WPS Office suite and complete the quick installation.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and easily open any existing .xlsx or .csv files exported directly from SharePoint.
3. Process Data Effortlessly: Use built-in pivot tables and data filtering tools to analyze your data without needing complex Power Query setups.

100% compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsLightweight architecture ensuring fast loading and reduced system resource usageFamiliar user interface requiring zero learning curve for Excel usersCompletely free for everyday data management and spreadsheet analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does SharePoint export IDs instead of names in Power Query?

Power Query accesses the underlying raw relational data of the SharePoint list. In SharePoint's backend architecture, Person or Group columns are stored as numerical lookup IDs linked to a hidden system directory, not as plain text names.

Can I fix the SharePoint ID issue directly within Excel Power Query without using Power BI or Power Automate?

Yes, but it requires advanced steps. You must connect to your SharePoint site's hidden 'User Information List' as a secondary data source within Excel's Power Query Editor, and then perform a 'Merge Queries' operation to link the numerical IDs to the names.

Does exporting to Excel directly from the SharePoint list view cause the same ID issue?

Usually, no. If you use the native 'Export to Excel' button directly from the SharePoint web interface, SharePoint automatically resolves lookup columns before exporting, displaying the actual user names in the resulting .iqy or .xlsx file.

Excel & Data Problems