Export SharePoint List Data for Power BI: Practical Checklist for SharePoint Owners

You need to bring SharePoint list data into Power BI for reporting, but the connection fails or the data looks wrong. The issue is that Power BI uses a specific connector that requires the correct list URL format and proper permissions. This article provides a practical checklist for SharePoint owners to export list data reliably to Power BI.

How Power BI Connects to SharePoint Lists

Power BI Desktop includes a native SharePoint List connector. This connector queries the SharePoint REST API to retrieve list items. The connector works with modern SharePoint sites and lists. Classic sites and lists may require the SharePoint Online List connector or a different approach.

Before you start, verify that you have at least read access to the SharePoint site. You also need Power BI Desktop installed on a machine that is joined to the same network or has internet access to the SharePoint tenant. The connector uses your current Windows credentials or a browser-based login if you choose the organizational account option.

What the Connector Supports

The SharePoint List connector can read single lines of text, multiple lines of text, choice columns, number columns, currency columns, date and time columns, yes/no columns, person or group columns, and lookup columns. It does not support managed metadata columns, external data columns, or calculated columns that reference other lists. File attachments are not included in the data export.

Checklist for Exporting SharePoint List Data to Power BI

Follow these steps in order to ensure a successful export. Each step includes the exact menu path or button name.

1. Open Power BI Desktop and select Get Data
   Launch Power BI Desktop. On the Home tab, click Get Data. From the dropdown, choose More. In the Get Data dialog, search for SharePoint. Select SharePoint List and click Connect.
2. Enter the SharePoint site URL
   In the SharePoint List dialog, paste the full site URL into the Site URL field. The URL must end with the site name, for example https://contoso.sharepoint.com/sites/Marketing. Do not include the list name or "/Lists/" path. Click OK.
3. Sign in with the correct account
   If prompted, select Organizational account and sign in using the same account that has access to the SharePoint site. The connector remembers your credentials for the current session. If you get a 401 error, your account may lack read permissions on the site.
4. Select the list from the Navigator pane
   After authentication, the Navigator pane shows all lists and libraries on the site. Locate your target list. Click the checkbox next to the list name. You can preview the first 200 rows in the right pane. Click Load to import all data or Transform Data to edit columns and filters.
5. Verify column mapping in Power Query Editor
   If you clicked Transform Data, the Power Query Editor opens. Check that each column has the correct data type. Person or group columns appear as records; expand them by clicking the expand icon (two arrows) in the column header. Remove any columns you do not need by right-clicking the column header and selecting Remove.
6. Apply data type changes and load
   After cleaning the data, click Close & Apply on the Home tab. Power BI loads the data into the data model. You can now create visuals, measures, and reports using the SharePoint list data.

Common Export Failures and How to Avoid Them

SharePoint List Connector Returns "We couldn’t authenticate"

This error usually means the URL is incorrect or the account lacks permissions. Double-check the site URL. It must be the root of the site, not a subsite or list path. Also confirm that your account has at least read access to the site. If your organization uses multi-factor authentication, use the organizational account option and complete the browser sign-in prompt.

Data Is Truncated or Missing Rows

SharePoint has a list view threshold of 5000 items. If your list contains more than 5000 items, the connector may only return the first 5000. To fix this, create a new view in SharePoint that filters the list to fewer than 5000 items. In Power Query Editor, you can also apply a filter before loading to reduce the row count.

Person or Group Column Shows IDs Instead of Names

The connector returns person columns as records containing the user ID and email. To see display names, expand the column in Power Query Editor. Click the expand icon, then select the fields you want, such as DisplayName and Email. Deselect the Use original column name as prefix checkbox to keep column names clean.

Lookup Column Data Does Not Appear

Lookup columns reference another list. The connector returns the lookup ID, not the display value. To bring in the display value, use the Merge Queries feature in Power Query Editor to join the lookup list. Alternatively, create a calculated column in SharePoint that pulls the lookup value, then export that column.

Item	SharePoint List Connector	SharePoint Online List Connector (Legacy)
Description	Native connector for modern lists	Older connector for classic lists
Authentication	Organizational account or Windows	Windows credentials only
Supported site types	Modern team and communication sites	Classic sites and some modern sites
Column support	Full support for most column types	Limited support; managed metadata columns may fail
Performance	Faster for lists under 5000 items	Slower; often hits throttling limits

Now you can export SharePoint list data to Power BI using the correct connector and URL format. Start with a small list to verify the connection works. Then apply filters and column expansions to shape the data before building reports. As an advanced tip, schedule a Power BI data refresh using a gateway to keep your reports up to date automatically.