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. You also need to 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. But, you will 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 “Datasouce 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.
If you want to know more
For more details about Connecting Dropbox Excel files to Power BI, using Excel, Power BI, and other advanced productivity tools, you can contact me here or request a custom offer on Upwork and Fiverr. It is also possible to have a direct relationship using Payoneer or PayPal. If you have some other preferred options for collaboration, please use this contact form for suggestions.
Share
