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.
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.
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:
Note: A dataset in the Power BI service refers to a model from a development perspective.
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.
- 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
Now, let’s dive deeper into How to Export SharePoint list to Power BI as a dataset step by step:
- Open your SharePoint List.
- From the above toolbar, click on “Export” > “Export to Power BI“.
- If you didn’t login to your Power BI Service, you will be asked to provide your Power BI credentials.
- Once, you login, you may need to repeat step 2 again to export your list to Power BI.
- 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.
- Click “Continue“.
- 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
- Start from Scratch
- Paginated Report
- Share the dataset
- Analyze in Excel
- Visualize the data by creating a report
To keep the data fresh after you’ve created the dataset, either refresh the data manually or set up scheduled refresh as stated below:
- From the Dataset details page, click on File > Settings.
- Scroll down to “Schduled Refresh“, and set it to On, then adjust your schedule as you prefer.
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.