How to Export SharePoint List Data for Power BI

You need to bring SharePoint list data into Power BI for analysis and reporting. Power BI can connect directly to SharePoint lists, but the method you choose affects refresh speed, data freshness, and security. This article explains the three main ways to export or connect SharePoint list data to Power BI: using the SharePoint List connector, the OData feed, and exporting to CSV or Excel. You will learn the exact steps for each method and which one fits your scenario.

Understanding SharePoint List Data Sources in Power BI

Power BI can consume SharePoint list data through several connectors. The choice depends on your list size, update frequency, security requirements, and whether you need scheduled refresh. The SharePoint Online List connector uses the SharePoint REST API and supports full refresh. The OData feed uses a standard protocol that some organizations prefer for governance. Exporting to CSV or Excel is the simplest method but lacks automation.

Before you start, verify that you have at least Read access to the SharePoint list. For scheduled refresh in the Power BI service, you need a gateway if your data source is on-premises. For cloud-based SharePoint, no gateway is required when using the native connector or OData feed.

All methods described work with SharePoint Online. SharePoint Server 2016 and 2019 require a gateway and the SharePoint Lists connector from the on-premises data gateway.

Method 1: Using the SharePoint Online List Connector in Power BI Desktop

This is the recommended method for most users. It provides a direct connection, supports scheduled refresh, and handles lists with thousands of rows. The connector reads the list schema and data through the SharePoint REST API.

1. Open Power BI Desktop and get data
   Click Get Data on the Home ribbon. Select Online Services from the categories, then choose SharePoint Online List. Click Connect.
2. Enter the SharePoint site URL
   Paste the full URL of the SharePoint site that contains your list. For example: https://contoso.sharepoint.com/sites/SalesTeam. Do not include the list name in the URL. Click OK.
3. Sign in to your Microsoft 365 account
   If prompted, sign in with an account that has read access to the list. Use the Organizational account option. Click Connect.
4. Select the list from the Navigator
   In the Navigator window, you will see all lists and libraries on the site. Check the box next to the list you want. You can preview the data on the right. Click Load to import the data, or Transform Data to edit columns before loading.
5. Configure scheduled refresh in the Power BI service
   After publishing your report, go to the Power BI service. Under Datasets, select your dataset and click Schedule refresh. Set the frequency and time zone. The refresh uses the credentials you provided during import.

This method works best when you need the data to refresh automatically and the list has fewer than 5000 rows. For larger lists, use the OData feed method or enable incremental refresh in Power BI Premium.

Method 2: Connecting via the SharePoint OData Feed

The OData feed exposes the list data as a RESTful service. Some organizations prefer this method because it does not require storing Microsoft 365 credentials in the Power BI dataset. The feed URL is generated by SharePoint and can be shared with report authors.

1. Get the OData feed URL from SharePoint
   Navigate to your SharePoint list. Click the gear icon and select List settings. Under the General settings section, click List name, description, and navigation. Copy the URL from the browser address bar. The URL ends with /AllItems.aspx. Replace AllItems.aspx with _api/web/lists/GetByTitle('YourListName')/items. Replace YourListName with the exact name of your list.
2. Open Power BI Desktop and get data from OData
   Click Get Data, then select Other and choose OData Feed. Click Connect.
3. Enter the OData feed URL
   Paste the URL you constructed in step 1. Click OK. If prompted for authentication, select Organizational account and sign in with your Microsoft 365 credentials.
4. Select the list items table
   In the Navigator, expand the feed to see the available tables. The table named value contains the list items. Check the box next to value and click Load or Transform Data.
5. Set up refresh in the Power BI service
   After publishing, configure scheduled refresh as described in Method 1. The OData feed credentials are stored separately in the Power BI service under Dataset settings > Edit credentials.

The OData method is useful when you want to avoid storing credentials in the .pbix file. However, the feed URL may change if you rename the list. This method also supports lists with up to 5000 items by default. For larger lists, add &$top=5000 to the URL and handle paging in Power Query.

Method 3: Exporting SharePoint List Data as CSV or Excel

This method is the simplest but least automated. It is suitable for one-time analysis or when you do not have Power BI Desktop installed. The exported file can be imported into Power BI, but it will not refresh automatically.

1. Export the list from SharePoint
   Open your SharePoint list. On the command bar, click Export, then select Export to CSV. If you prefer Excel, click Export to Excel instead. Save the file to your local drive.
2. Import the file into Power BI Desktop
   In Power BI Desktop, click Get Data, then select File. Choose CSV or Excel depending on the file type. Browse to the file and click Open.
3. Transform and load the data
   In the Navigator, select the sheet or table. Click Transform Data to clean columns, change data types, or remove unnecessary columns. Click Close & Apply to load the data.
4. Manually refresh the data
   When the list changes, you must repeat the export and reimport the file. There is no automatic refresh. To minimize manual work, save the CSV file to a SharePoint document library and use the SharePoint Folder connector in Power BI to read the file.

This method is not recommended for production reports because it lacks automation and can lead to stale data. Use it only for ad-hoc analysis or prototyping.

Common Mistakes When Exporting SharePoint List Data for Power BI

SharePoint List Connector Shows No Lists

This happens when the site URL is incorrect or the account does not have permissions. Verify that the URL points to the site root, not a subsite or the list itself. The connector requires at least Read access at the site level. If you are a member of a Microsoft 365 group, the site is provisioned automatically, but you may need to browse to the site first.

OData Feed Returns a 403 Forbidden Error

The OData feed uses the SharePoint REST API. A 403 error means the API endpoint is blocked or the account lacks permissions. Check that the list name in the URL exactly matches the list title including spaces. Also verify that the site does not have custom API access policies that restrict REST calls.

CSV Export Contains Only 100 Rows

SharePoint limits the CSV export to 100 rows by default. To export all rows, use the Export to Excel option, which supports up to 10,000 rows. For larger lists, use the SharePoint Online List connector or OData feed. If you must use CSV, split the data using views with filters and export each view separately.

SharePoint List Connector vs OData Feed vs CSV Export: Key Differences

Item	SharePoint Online List Connector	OData Feed	CSV Export
Authentication	Microsoft 365 credentials stored in dataset	Microsoft 365 credentials stored in service	None required
Scheduled refresh	Supported	Supported	Not supported
Maximum rows	5000 (default), more with pagination	5000 (default), more with pagination	100 (CSV) or 10,000 (Excel)
Setup time	5 minutes	10 minutes	2 minutes
Best for	Production reports with regular updates	Scenarios requiring credential isolation	Ad-hoc analysis and prototyping

Now you can choose the right method for your SharePoint list data. For automated, refreshable reports, use the SharePoint Online List connector. If your organization requires separate credential management, use the OData feed. Reserve CSV exports for quick one-time tasks. After connecting, consider adding calculated columns in Power Query to transform list data before building your report.