Logo
banner

Blogs

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

, March 12, 2021 25870 Views

Power BI supports many data sources from where we can fetch data, 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 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 Power BI Desktop. In 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.

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. Instead, these local files can be synced with OneDrive and then using this technique, the files could be accessed into Power BI.

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

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

One response to “Power BI > Get Data from OneDrive for Business (Folder as a source)”

  1. Peter Kucerik says:

    Hello,

    many thanks for your blog, it helped me a lot

    Is it possible to automate this? For example: everyday check onedrive folder and merge all (or append data from new file) from onedrive folder and update dashboards?

Leave a Reply

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

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Would you like to digitize your business and put it on the cloud?
Do you need clear, concise reports for your organization?