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
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
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]) )
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″.
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 🙂