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 (9) 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

Leave a Reply