Edit 2nd October 2021: Unfortunately this method doesn’t work any more, as Dropbox now requires proper OAuth authentication.
Below you’ll find a video where you can see how easy it is to import multiples files from a Dropbox folder into PowerBI or Excel at once.
There are 2 different methods to grant access to your Dropbox: Grant access to the whole Dropbox or to a (newly created ) folder only. I will present the folder-method, as granting access to your whole Dropbox is really dangerous in my eyes – unless you are prepared to share it all publicly: The token generated will allow anyone to read your data. So also all those people who you’ve sent this beautiful dashboard where you just forgot that it contained your token…
To make it super-easy for you, I’ve created a function that you can download here: fnDropboxFolder
The code for it I’ve got from this thread in the PowerBI forum, which contains some additional useful information and a link to a solution with a custom connector for PowerBI, making it easy to deploy in a corporate environment (designed by Igor Cotruta).
Just watch how it works:
Some screenshots to follow along:
1 Open your Dropbox and to to settings
2 Scroll down and choose “Developers” 
3 Select “My apps” and click “Create app”
4 Choose “Dropbox API” -> “Full Dropbox” -> And give it a nice name
5 Simply click on “Generate”, copy the token and save it for later use
6 Download the “fnDropboxFolder”-file and copy the content
fnDropboxFolder
7 Open pbi and create a new blank query
8 Open this query in the advanced editor
9 Check all content there and replace it with the copied code
10 Paste your API-token into the first field of the function dialogue
The files come in the column “File” as a binary. To open them, you create a column where you open them with the appropriate function. This is “Excel.Workbook” for xlsx-files and for other files formats check out the functions here.
Quick-tip to access csv-files sheets
If you open csv-files, their first rows will not be headers by default. If you wrap the opening-function: “Csv.Document” into a “Table.PromoteHeaders”, they will behave:
Table.PromoteHeaders( Csv.Document([File]) )
Sub-Folders
You can create sub-folders in the main folder and to access them, you just pass the string into the second function field “Folder”. Make sure you always begin with a slash like this: “/Subfolder”. Nested subfolders will be accessed like this: /Subfolder1/Subfolder2″.
Further customization
If you want to customize this import further, check out the documentation here. It’s amazing what options are available: You can page your query if the number of files is too large or even get a cursor for your latest query. So in theory, you could do incremental loads …once this will be supported in PBI 🙂
Edit 18-Nov-2017: This solution will not work in PBI Service. Therefore you have to choose this method: Joe Fusaro Blogpost. Big thanks Joe Fusaro for this!
Enjoy & stay queryious 🙂
Pingback: #Excel Super Links #84 – shared by David Hager | Excel For You
i would be great if this code of yours works also on Power BI service, but the way it is it does not work.
Yes, unfortunately there are many features that don’t work in the service.
Please find a great method that works in the service as well here: Joe Fusaro Blogpost
It’s a miracle! It works, thanks a looot!
My pleasure – you’re welcome 🙂
Hi
Could you, please tell me, is it possible to share Apps folder within my team? I found way where they can only view files inside this folder. Thanks!
Hi Kirill,
what exactly do you mean with “Apps folder”?
Nice one!
Thank You!
Hello!
I ask for your help, if it is possible: I used the method and the function that you made available and everything worked perfectly in a case. But when using in another identical case, the action always ends in error (… 400 – Bad request …). I have carefully examined all the details and can not find anything different that might cause the error. Apparently. I need to import into Power BI files in Excel (“.xls” or “.xlsx”) and these files are in a Dropbox folder where they will be periodically updated by the ERP software used by the client. Can you help me?
Are you trying this in the Desktop-version or in the service/web?
On Desktop-version.
What was different in the “another identical case”?
If you connect to a different dropbox-account for example, you need a different dropbox-token.
Also make sure that the path-specification ends with a slash “/”.
Otherwise you might try this alternative solution here: https://medium.com/@JoeFusaro/connecting-an-excel-workbook-stored-in-dropbox-with-power-bi-and-enabling-scheduled-refresh-53cb3a675f57
Yes, I was careful to check everything: I’m using another account, so another token. What I noticed recently is the existence in this new account of another API, giving full access to Dropbox, not just the folder. I did another test, on a third account, without this API, and this time, like the first case, it worked. I suppose this API giving full access to the Dropbox folder is causing the problem. I’ll check out this other solution. Thank you very much for your attention! If I confirm my hypothesis, I’ll answer you to register and maybe help other developers.
Thanks!
Hello!
As I promised, I’m giving a return on the problem: in fact, the API giving full access to Dropbox was the cause of the problem. We deleted it, and made a new API, with folder permission, and the method and function worked perfectly. You’re brilliant, and I do not know how to thank you for your invaluable help. Thank you!
You’re welcome & thanks for feedback!
Hi Imke,
First of all I wish you a healthy and happy newyear.
I am a big fan of Löw and the Mannschaft and I think it will be a great year again (and I am from Holland)
I have two questions? I am pretty desparete to create a Dropbox sollution form my Power BI. In my Dropbox I will create an App Folder (DropDrive for example), Below that I have two levels of mappings that would result in something like:
https://www.dropbox.com/home/Apps/DropDriveJcxel BI60819_BI_DatabasesDbAccesDbRGS.accdb
I have watched your great presentation ‘ImportFromDropboxFolder’ at https://www.youtube.com/watch?v=eEVRO-Zc7pg. I also have read ‘Integrations with Files and Services’ at https://community.powerbi.com/t5/Integrations-with-Files-and/Connecting-to-data-source-hosted-on-Dropbox/td-p/67946 in which you also made valuable contributions.
I could not find final answers to the following questions:
• Do you know if there is specific instruction for the configuration of a folder structure in Dropbox?
I have one folder 60819_Databases in which I use filter databases in Acces format. This folder I use in various Power BI Desktop files.
I have another folder 60820_BI_Reports. In that folder I have different mappings with Excel files with the movements. Per map I use one ore more specific files in the Power BI Desktop files.
So for a Power BI Desktop model I normally use two folders on my DropBox (one in 60819 and one in 60820).
• Do you know if there is the same type of code as in the file for the v2 API version of Dropbox?
What I understood from the remarks from Mike Honey the v2 API version is way more complex and the code of Hugo Berry is not the same as yours.
I have been struggling for a couple of months now to create a proper setup with OneDrive that does not work yet. This is more then depressing to because I am in the process of starting my own company.
You really would help me out if you can guide me to success with this DropBox option.
Many thanks in advance.
Jack
Hi Jack,
thanks for the wishes an a happy new year for you too!
Unfortunately I have no solutions for your questions.
You might consider posting your question in the PBI community-thread that you’ve mentioned above instead.
Cheers, Imke
I have set this up and it’s opening the files in the folder I need but not the actual csv file i.e. just the folder with the file names. How do I like to the actual csv file?
Good evening Imke,
I’ve been using your function for quite a while, with lots of pleasure, but since today, it is giving a time out. It seems that Dropbox also updated their client version yesterday to 87.4.138.
Have you encountered any issue from your side as well? If yes, have you find a way to solve it?
Kind regards,
Lohic Beneyzet
Hi Lohic,
currently I’m not actively using it. Please keep me updated if the problems continue and I will check it out.
Thanks and cheers, Imke
Good evening Imke,
I think there was an hick-up somewhere in the report and not in the function ;-@
Thanks for your quick reply and keep up the good work.
I wish you already a Merry Christmas, an Happy New Year and lots of data driven decision fun in 2021.
Thanks, same to you!
It seems that it doesn’t work now, maybe Dropbox has made changes on API side, but what I receive each time – trying carefully described steps – is error 400: Bad Request.
Yes, thanks for pointing this out.
Now, only proper OAuth seems to work, so a single token isn’t enough any more.
Sorry, but you’d have to create a custom connector for this.
Thanks for the response.
As for custom connector – do you have any reference to how-to source?
Hi Dariusz,
this is a gentle introduction: https://jussiroine.com/2019/02/building-a-custom-connector-for-power-bi-that-supports-oauth2-to-visualize-my-wellness-data/
And the official docu is quite useful as well: https://docs.microsoft.com/en-us/power-query/installingsdk
Hi Imke,
thank you for the references.
Hi,
Just to let you know that it still works as of June 2022. Thanks a lot Imke!
I will try in PBI Service and let you know.
Alexandre
Imke’s function worked in Power bi service. I could refresh the dataset and schedule it also.
The only issue is I need to generate the token once more everytime I went to app settings in the Dropbox appcenter.
For exemple, I had to give the file read permission and then generate it again. Also, one really need to test with Dropbox API explorer before moving to Power BI.
Many thanks!
Hi Imke,
I was trying to set things up as you explained in the video. All worked well until I invoked the Query after entering the API-token. Then I get following error message:
An error occurred in the ‘Query1’ query. DataSource.Error: Web.Contents failed to get contents from ‘https://api.dropboxapi.com/2/files/list_folder’ (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.dropboxapi.com/2/files/list_folder
Url=https://api.dropboxapi.com/2/files/list_folder
Not sure what all of this exactly means. I did first try with a number of CSV files in the folder of the app and then did the same with a few XLS files but all with the same result.
Can you guide me a bit please to find out what is wrong.
Many thanks
Paul