Blogs

Power BI > Get Data from OneDrive for Business (Folder as a source)

Inkey, March 12, 2021 35365 Views

Microsoft Power BI supports many data sources from where we can fetch data and a good Microsoft Power BI Consulting services provider can help, but many times, we need to use some workaround to fetch data from certain data sources. One such case is to get data from a folder on OneDrive. There are no in-built data connectors available in Microsoft Power BI Desktop using which we can directly import all the files from the folder on OneDrive. Now, the question is – How to get the data from these files into Power BI?

Note: Power BI can read data only from “OneDrive for Business” & not from the personal OneDrive. But for abbrevity, I will be using the term OneDrive & not “OneDrive for Business” in this blog.

Let’s look into the solution which will guide you step by step in an easy and understandable manner.

First of all, all the files present in the OneDrive folder should have the same structure. In this example, we have three excel files with the same structure in the ‘Test Files’ folder of Onedrive.

The next step is to find the web URL to the folder, i.e. the actual path of the folder where the excel files are located. For this, the following steps needs to be followed:

• Open any one of the excel files in MS-Excel installed locally.
• Go to the File menu.
  • Under the Info tab, we can see Open File Location (under Related Documents) to the right bottom.
  • Right-click on that.
  • Select Copy path.

After copying the file location, close that excel file and open the Microsoft Power BI Desktop. In Microsoft Power BI Desktop, click on Transform Data to open the Power Query Editor window.

In that, click on New Source and then select Blank Query.

 

---

Discover the power of our services:

Microsoft Fabric, Microsoft Power BI Consulting, Microsoft Business Intelligence, Microsoft SQL Server, and Dynamics 365 Business Central. By the power of these services, from advanced analytics to seamless business integration, we’ve got the expertise you need to optimize operations and drive growth. Harness the potential of your data infrastructure with our comprehensive suite of solutions.

---

 

After this step, we have to edit the query in the Advanced Editor. For this, please follow below steps:

• Click on the Advanced Editor in the Power Query Editor window.
• Write the web URL source (which we copied from the excel file) as mentioned below:
  Source = SharePoint.Contents(“Place the web URL here”)
• Click on Done.

After this, we will get a table from which we have to select the location of the folder. As we have stored our ‘Test Files’ folder in Documents, click on ‘Table’ under the Content column as shown below:

Now we will be able to see some sort of data. Combine the files by clicking the symbol beside the ‘Content’ column (in the column header) as shown below.

Note: If we want to combine two files out of three files, then we have to select those two files by clicking on the down arrow beside the ‘Name’ column (in the column header) and then combine the files by clicking the symbol beside the ‘Content’ column.

And voila, you have all the data from all the files in OneDrive folder.

This way you can get away with installing Power BI Gateway to install local files and is also a part of our Microsoft Power BI Consulting services. Instead, these local files can be synced with OneDrive and then using this technique, the files could be accessed into Microsoft Power BI.

Please let us know if you have any queries or suggestions in the comments.