![[How To] Import Data from a File Stored in SharePoint into Power BI](https://goanalyticsbi.com/wp-content/uploads/2023/01/Connect-to-SharePoint.png)
In this blog post, we provide a step-by-step guide on how you can import data from a file stored in SharePoint into Power BI.
One of the top reasons for choosing Microsoft Power BI over other data visualization tools is the integration with Microsoft’s suite of products – and one of these integrations is SharePoint. Microsoft makes it easy for users to connect to their SharePoint files from Power BI.
Let’s start by understanding the basics of SharePoint. SharePoint is a web-based platform that allows users to store, organize, and share files and data across and outside their organization. It is commonly used by organizations as a central repository for documents, images, and other files. In some cases, commonly used datasets are stored and maintained in SharePoint.
To connect to a data file stored in SharePoint in Power BI, you will need the web address that you use to access your SharePoint site. If you use Microsoft Teams to access your SharePoint files, you can click on the ellipsis […] and then select Open in SharePoint to open it in a browser. You can then copy your URL from there.
Once you have the URL for your SharePoint site, open the Power BI Desktop application and select Get data from the Home tab and click More… to open the full list of data connectors available in Power BI. From there, you will be presented with a list of data sources that you can connect to. You can select SharePoint folder from the list of options or use the search bar to search for “SharePoint”. Note that the latter method will also bring up the SharePoint list and SharePoint online list connectors, which are not the same as SharePoint folder.
Need Help Getting Started With Power BI?
Our Microsoft Certified consultants can help with the implementation of Power BI in your organization
Next, you will be prompted to enter the URL for your SharePoint site. Once you have entered the URL, you will be prompted to enter your SharePoint credentials. If you are already logged into SharePoint, you may not need to enter your credentials.
Once you have successfully connected to your SharePoint site, you will be presented with a list of files that you can connect to. At this point, Power BI will offer you the options to Combine, Load, or Transform your files. Let’s explore what these options mean when connecting to your desired data file.
Choosing the Combine option will take all your files and combine them into one query. This is ONLY ideal if you have a SharePoint folder with multiple files that follow the exact same data structure (i.e. same file type, number of columns, and column names). An example would be monthly sales data where each file represents a calendar month, but the files all follow the same structure.
Choosing the Load option will load of your data into the data model. If you have many data files that don’t need to be loaded into Power BI or other file types, this is not the right selection. This option would only be ideal if you have a single or a few data tables that you want to visualize AND that are ready for analysis (i.e. no data cleaning necessary).
Choosing Transform Data will open the Power Query Editor, where you can make modifications to your data including eliminating files you don’t need. This is ideal if you have a lot of files in your SharePoint folder.
In majority of cases, it is best practice to select Transform Data so that you can be sure you are only loading what you need into Power BI.
Once you identify the file you want to import data from, click the Binary link next to your file name from the Content column. This will open your data file as a query in Power Query and you can then make any data manipulation before loading it to the data model.
After you are done your data manipulations, click Close & Apply in the Power Query Editor to load your data to the data model and start visualizing it.
You can then use the various tools and features in Power BI to create visualizations, charts, and other data representations based on the data in your SharePoint file.
Connecting to a data file stored in SharePoint in Power BI is a simple and straightforward process. With the ability to connect to a variety of data sources, Power BI provides a powerful tool for visualizing and analyzing data, which can help organizations make better data-driven decisions.
![[How To] Change Data Source Location in Power Query](https://goanalyticsbi.com/wp-content/uploads/2023/07/Change-Source-Data-Location-300x157.png)
Hey would you mind sharing which blog platform you’re working with?
I’m planning to start my own blog soon but I’m having a hard
time deciding between BlogEngine/Wordpress/B2evolution and Drupal.
The reason I ask is because your design seems different then most
blogs and I’m looking for something completely unique.
P.S Apologies for being off-topic but I had to ask!
Hello – no problem for the off-topic question. I am running my blog on WordPress and I use the OceansWP theme.Good luck with your block!