Export SharePoint list to Power BI

Export SharePoint list to Power BI as a dataset

In this post, we will learn How to Export SharePoint list to Power BI as a dataset to can quickly create a Power BI dataset directly from a SharePoint list.

Export SharePoint list as a dataset to Power BI
Export SharePoint list to Power BI

In SharePoint Online Modern List, you can now quickly create a Power BI dataset directly from a SharePoint list to prepare your SharePoint list data for reporting and visualization in Power BI.

Export SharePoint list to Power BI
Export SharePoint list to Power BI

But before we getting started to learn How to Export SharePoint list as a dataset to Power BI, let’s first know more about the Dataset in Power BI Service.

What’s the Power BI Dataset?

In Power BI, a dataset is a collection of data that has been imported, transformed, and organized to support reporting and analysis.

  • A dataset typically contains one or more tables of data, each with its own columns and rows.
  • In Power BI, you can import data from a wide variety of sources, such as Excel workbooks, CSV files, SQL Server databases, or cloud-based sources like Azure SQL Database or SharePoint Online. Once you have imported the data, you can transform and clean it using Power Query, a powerful data transformation and cleansing tool within Power BI.
  • After you have created a dataset, you can use it to build visualizations and reports in Power BI. You can also share the dataset with other users in your organization, who can use it to create their own reports and dashboards.
  • Power BI allows you to create multiple datasets in a single report, which can be useful if you want to combine data from different sources or if you need to analyze data in different ways. You can also schedule data refreshes to keep your datasets up to date with the latest data from your source systems.

You can browse the created dataset in Power BI Service, in the Data Hub section as shown below:

Data Hub in Power BI Service
Data Hub in Power BI Service

Note: A dataset in the Power BI service refers to a model from a development perspective.

Create SharePoint List as Dataset to Power BI

After gaining a grasp of the dataset and its location within the Power BI service. let’s first list the limitations and considerations of exporting SharePoint list as a dataset to Power BI.

Export SharePoint list to Power BI Considerations

  • You must have a Power BI account to be able to use this functionality. If you’re a free user in Power BI, you’ll only be able to save to My workspace.
  • The dataset will be saved in the specified workspace, and everyone with the sufficient permissions in this workspace can use it
  • To keep the data fresh after you’ve created the dataset, either refresh the data manually or set up scheduled refresh.
  • If you need to model or transform the data in ways that aren’t available in Power BI service, you can connect to the SharePoint list from Power BI Desktop

Read more at Considerations and limitations

How to Export SharePoint list to Power BI?

Now, let’s dive deeper into How to Export SharePoint list to Power BI as a dataset step by step:

Steps

  1. Open your SharePoint List.
  2. From the above toolbar, click on “Export” > “Export to Power BI“.
Export SharePoint list to Power BI
Export SharePoint list to Power BI
  1. If you didn’t login to your Power BI Service, you will be asked to provide your Power BI credentials.
  2. Once, you login, you may need to repeat step 2 again to export your list to Power BI.
  3. Now, you will be asked to provide the Dataset name as you prefer (default is the list name), as well as the workspace that you would like to save your dataset.
  4. Click “Continue“.
create SharePoint list dataset to Power BI
Create SharePoint list dataset to Power BI
  1. Now, you will be navigated to the Dataset details, with more options you can to do such as
    • Visualize the data by creating a report
      • Auto-Create
      • Start from Scratch
      • Paginated Report
    • Share the dataset
    • Analyze in Excel
Dataset details in Power BI service
Dataset details in Power BI service

Keep SharePoint List Dataset Up to Date

To keep the data fresh after you’ve created the dataset, either refresh the data manually or set up scheduled refresh as stated below:

Steps

  1. From the Dataset details page, click on File > Settings.
Dataset settings in Power BI Service
Dataset settings in Power BI Service
  1. Scroll down to “Schduled Refresh“, and set it to On, then adjust your schedule as you prefer.
Dataset Schduled Refresh in Power BI
Dataset Schduled Refresh in Power BI

Conclusion

In conclusion, creating a well-structured and organized dataset in Power BI is essential for efficient data analysis and visualization.

Using SharePoint list as a dataset in Power BI can provide many benefits for organizations looking to analyze their data. SharePoint lists can easily be imported into Power BI, allowing users to create powerful data visualizations and gain insights into their data.

Additionally, since SharePoint is a widely used platform, many organizations already have data stored in SharePoint lists, making it an ideal data source for Power BI.

Furthermore, the ability to refresh and update the dataset automatically ensures that insights and visualizations remain up-to-date, providing valuable information for decision-making.

Overall, by leveraging the power of Power BI and SharePoint together, organizations can make more informed decisions and drive business value from their data.

See Also

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top