Others

How to Fix SharePoint List ID Column Showing as List in Power BI

John Wilson Sep 27, 2026 868 views

Question details

The user needs to retrieve and display actual text names instead of "[List]" or numeric IDs for SharePoint person or lookup columns when importing data into Power BI.

How to Fix a SharePoint List ID Column Showing as [List] in Power BI

Product: Power BI
Device & OS: not provided
Scenario: Importing a SharePoint list into a Power BI dataset for data visualization and reporting.
Observed behavior: SharePoint person or lookup columns appear as "[List]" in Power BI, and expanding them only exposes numeric IDs rather than the expected display names.

Before you start

Ensure you have the necessary permissions to edit the dataset in the Power BI Power Query Editor, and check your access rights to the source SharePoint list if you need to create a workaround column.

Solution 1Recommended

Expand the Nested List or Record in Power Query

The most direct way to resolve this issue is by expanding the nested lists or records directly within the Power Query Editor to extract the underlying display names.

SharePoint stores complex data types like Person or Lookup columns as nested tables or records. Power BI imports this raw structure by default, requiring you to manually navigate and expand the hierarchy to access specific text properties.

Open Power Query Editor

In Power BI Desktop, click on "Transform Data" from the Home ribbon to open the Power Query Editor.

Locate and Expand the Column

Find the SharePoint column that is currently displaying as "[List]" or "[Record]". Click the expand icon (two diverging arrows) located in the upper right corner of the column header.

Extract the Display Name

If prompted, select "Expand to New Rows". Click the expand icon again and select the specific field containing the text value you need (such as "Title", "DisplayName", or "FieldValuesAsText"). Uncheck "Use original column name as prefix" and click "OK".

Expand the Nested List or Record in Power Query

Check Data Types: After expanding the column, ensure you change the new column's data type to "Text" so it behaves correctly in your Power BI visuals.

Solution 2

Create an Automated Helper Column in SharePoint

If Power Query fails to expose the required name field due to API limitations, create an automated text column in SharePoint to capture the display name at the source.

Free Microsoft Office alternative

Try WPS Office for Your Data and Document Needs

While Power BI handles complex data modeling, you may need a lightweight, cost-effective solution for everyday spreadsheets, reports, and documentation. WPS Office provides a complete, free alternative to Microsoft Office, ensuring seamless format compatibility for all your business tasks.

1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install the Software: Run the downloaded file and follow the on-screen instructions to install the comprehensive office suite.
3. Open Your Data Exports: Launch WPS Spreadsheet to quickly open, view, and analyze data exported from SharePoint lists or Power BI reports.

Highly compatible with Microsoft Excel (.xlsx) and CSV data exportsLightweight architecture that runs smoothly on older devicesFamiliar ribbon interface requiring zero learning curveBuilt-in PDF editing and format conversion toolsFree to download and use for your daily office workflows

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power BI show SharePoint person columns as [List]?

SharePoint structures complex data types, such as Person or Lookup columns, as nested arrays (tables or records). Power BI imports this raw structural format. You must manually expand the list within Power Query to access the individual underlying properties, such as the person's name or email.

How do I fix the numeric ID showing instead of a name?

When expanding a lookup column in the Power Query Editor, you will see a list of available attributes. Ensure you check the specific attribute containing the text value (like 'Title' or 'FieldValuesAsText') instead of, or in addition to, the 'ID' field.

Where can I get help with complex Power BI data modeling?

If you encounter advanced data modeling issues when connecting SharePoint to Power BI, it is highly recommended to post your question with screenshots in the official Microsoft Power BI Community forums to get assistance from data modeling experts.

Office Quick Solutions