cancel
Showing results for 
Search instead for 
Did you mean: 
Djordje_M

Connecting Dropbox Excel files to Power BI

Connecting Dropbox Excel files to Power BI can be a challenging task. If you are an Excel and Power BI user, in most cases, your Excel file will be on your computer in One Drive. But some users can keep their Excel files on Dropbox. As there is no Dropbox connector in Power Bi, these steps are needed to connect and successfully refresh the Excel data source from Dropbox.

Keeping the files on your local computer can work for you. But, if you want to have a published Power BI report with a scheduled refresh, it can be a problem. In that case, you would need to set up a personal gateway and keep your PC on during the scheduled refresh time period. This is not that comfortable, isn’t it? The other option is to keep your Excel file on One Drive, and it will be like that in most cases.

 

Setting up the data source

Sometimes, for various reasons, you will keep your Excel files on Dropbox instead and still need to publish and refresh your Power BI reports.

For the initial report definition, you will the standard “Get data from Excel” option and load the file from the Dropbox folder on your computer. When you finish setting up your Power BI report, and if you want to publish it and schedule refresh on Web Services, you would like to change your data source to the one in the cloud.  So how to do that?

 

Step 1.

  • Select the file in File Explorer and select the “Copy Dropbox Link” option. You may want to paste it into a notepad.

 

windows.jpg

 

  Step 2.

  • In the Power BI desktop report, choose Transform Data and then go to Advanced Editor. You will see a line similar to this one: Source = Excel.Workbook(File.Contents("C:\Users\djordje\Dropbox\tabele\troskovi 2021 universal.xlsx"), null, true),
  • Replace the “File.Content” with “Web.Content” and paste the Dropbox link that we get earlier instead of the filename. Change the statement “dl=0” to “dl=1” to tell Dropbox we want the Excel file and not the HTML one. Your line will look similar to this one: Source = Excel.Workbook(Web.Contents("https://www.dropbox.com/s/s2ps9hgjjmipapo/troskovi%202021%20universal.xlsx?dl=1"), null, true),

transform.jpg

EXCEL.jpg

 

dropbox.jpg

 

  •  Close and apply changes and refresh the report.

Step 3.

  • After publishing your report to Web Services, you will need to edit Dropbox credentials in the “Datasource Settings” then “Data source credentials” options and check if the “Scheduled refresh” is turned on.

PBIWEB.jpg

From now on, you will get a regular Dataset refresh for your Power BI report in Power BI Web Services.

 

Further steps

 

Have you ever have to connect and schedule refresh for your Dropbox Excel files in Power BI? I needed to do this recently, and that is how I did it. If there is a simpler way, please let me know. I will be happy to update this article with the necessary details. Also, you can check for more information on my blog.

Comments
Polls
What is your favorite Power BI Feature release this month?