Connecting Dropbox Excel files to Power BI

[Djordje_M]

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.

  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),

•  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.

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.

[Anonymous]

how can i connect my local system to the server thorugh SQL instance( if i am within the same Network)??

i tried the standard ways of get data and import. it doesnt authenticate me into the Database.

[Djordje_M]

@Anonymous  To be honest, I did not try this by myself, but this article should answer your question https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

Basically, you need to choose the correct authentication method and have appropriate rights to use the data. I hope this will help.

[Fabio_Roberto]

@Djordje_M  Thanks for the article, I got it without problems

Is it possible to connect to the dropbox folder? because I have a connection to folders

[GabrielFalconi]

Thank you, my friend. It helped me a lot.

Just for your to know, your link is public and anyone on web can see it.

@Djordje_M 

[Rakshhit_Sharma]

I have a question . the dashbaord uploaded on service is working fine with excel files of size less than 15 mb, but as soon as it gets over 16mb, connectivity error happens . any suggestions what can i do with xlsb file present in dropbox?