Import multiple files from Dropbox folder into PowerBI and Excel (via PowerQuery) at once

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 🙂

Comments (20) Write a comment

  1. Pingback: #Excel Super Links #84 – shared by David Hager | Excel For You

  2. i would be great if this code of yours works also on Power BI service, but the way it is it does not work.

    Reply

  3. 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!

    Reply

  4. 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?

    Reply

  5. 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!

    Reply

  6. 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/DropDrive\Jcxel BI\60819_BI_Databases\DbAcces\DbRGS.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

    Reply

    • 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

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz