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:
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:
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.
© All Rights Reserved. Inkey Solutions 2021