cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnSwid
New Member

Connecting to data source hosted on Dropbox

Hello PowerBI-Ites,

 

Has anyone had any luck connecting to Dropbox as a data source.  Couldn't find many articles on this topic.  Here's one  article that had a solution we couldn't get to work: http://community.powerbi.com/t5/Desktop/Data-source-hosted-on-Dropbox/td-p/47882.

Just wondering if anyone could shed some light on this topic.  Thank you!

 

58 REPLIES 58
v-yuezhe-msft
Microsoft
Microsoft

Hi @JohnSwid,

Which file or data source do you store in Dropbox? I am trying to connect to a Excel file stored in DropBox following ankitpatira’s suggestions and everything works well.
1.PNG

Moreover, some ideas about that connecting to data source stored in Dropbox have been submitted in the following links, please vote them. Some changes may be made for the connection to Dropbox and it could be easier for us to connect to Dropbox from Desktop in the future.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6663925-dropbox  
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9045649-please-add-more-data-serv...

In addition, you can also consider to store files in OneDrive instead of  Dropbox and connect to them from Power BI Desktop.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

While researching this topic, I noticed that all the examples given appear to be for the Dropbox v1 API, which has been deprecated and will be turned off in a few months (June 2017):

 

https://blogs.dropbox.com/developers/2016/06/api-v1-deprecated/

 

The v2 API looks much more complex - there doesnt appear to be any direct equivalent for the method suggested above (single HTTPS call with an API Key). The authentication requirements now seem more complex:

 

https://www.dropbox.com/developers/reference/auth-types#user

 

It's stumped me for now - has anyone got this working?

I agree with @mike_honey - cant seem to get it working.

Any tips on connecting into the dropbox files would be great.

 

The documentation on their developer site is quite comperhansive https://www.dropbox.com/developers/documentation/http/documentation

As for some boilerplate code to get you going have a look at the snippet below for an example of geting folder listing.

let
    data = [    path= "/code",
                recursive=false,
                include_media_info=false,
                include_deleted=false,
                include_has_explicit_shared_members=false],
    header = [  #"Authorization"="Bearer ZZZZZZZZZZZ",
                #"Content-Type"= "application/json"],
    response = Web.Contents("https://api.dropboxapi.com/2/files/list_folder",[Content=Json.FromValue(data),Headers=header]),
    out = Json.Document(response,1252)
in
    out

The only setup that I had to do on the Dropbox site was to create an App in the developer space and then generate an access key, which by the way is ZZZZZZZZZZ in my snippet.

masplin
Impactful Individual
Impactful Individual

I tried your code and got this error. I just created an app and added the access code where you had zzzzzzz.

 

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

 

Any ideas what this means?

 

Thnaks

Mike

hugoberry
Responsive Resident
Responsive Resident

I have created a DropBox data connector using the Power Query Extensions SDK. You are welcome to try it or check the code for a possible answer
https://github.com/Hugoberry/PowerQueryConnectors/blob/master/src/DropBox/DropBox.pq
masplin
Impactful Individual
Impactful Individual

Actually I managed to set up the new personal gateway so that i coud use the files on dropbox as a data source for a PBIX file

 

I think thisis doing somethnig different? Coudl you just explain in what instances I woudl be looking to use this for futre refernece?

 

Thnaks

Mike 

Hi guys,

thanks for these helpful different solutions! I've picked up some code bits from this thread and created a function that makes it pretty easy to grab files from a dropbox-folder: https://www.youtube.com/watch?v=eEVRO-Zc7pg

 

This should be helpful for folks who are not so tech-savy and might stumble across this post.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

masplin
Impactful Individual
Impactful Individual

Hi Imke

 

I set out to follow you instruction to use the App folder instead of a normal dropbox folder which seems to be almost impossible. Anyway i created my app and put some files in the folder.  However I got imeddiately stuck because you can't share an app folder.  We have multiple users sharing our BI dropbox and any of them might be adding new files tot the fodlers each month. So if the App folder can't be shared does that mean the only way to get new data in an App folder is to login to Dropbox as the app owner?  Just seems a real pain if thisis the case as otherwise your solution looks elegant.

 

I realsie thisis a dropbox questionnot a PowerBI question, but thoguht you might know if any way round this.

 

Thanks

Mike

Hi Mike,

having difficulties to follow you here: What do you mean with "App folder"?

To my understanding you need to create an App in Dropbox to be able to generate an API-key. For the use in PBI you don't need the Dropbox-app explicitely, just the key/token.

The folder you can choose is just a "normal" folder in the dropbox.

But I might be totalally mistaken here, actually I don't use this myself.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

masplin
Impactful Individual
Impactful Individual

In your post you put the files inside the App folder so assumed you had to do that. I'll go and try with a normal dropbox folder. and report back

masplin
Impactful Individual
Impactful Individual

Ok so I used your code and token I had got for the whole of my dropbox. If I put no folder path I get somethnig

 

Capture.PNG

 

However i need a folder that is several layers down  Datamodel/3rd party Bookings/WCFMC

 

If i put in anything in the folder bit it errors

 

Capture.PNG

 

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

 

Any ideas how to get it to read the contents of subfolders?

 

Thnaks

Mike

 

 

Folder need to start with a slash:

 

DropboxSubfolders.jpg

see here: http://www.thebiccountant.com/2017/06/25/import-multiple-files-dropbox-folder-powerbi-excel-via-powe...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

masplin
Impactful Individual
Impactful Individual

Ah perfect. looks like its working. Fantastic.

 

your the best

 

Mike

masplin
Impactful Individual
Impactful Individual

Sadly the previous poster is correct. your method works within excel and PowerQuery, but you cannot use that same query in PDI Desktop as fails on the gateway

 

Capture.PNG

 

 

That is really annoying.

 

Mike

Really? That is a pain!

Actually, I don't have a gateway in place on my local machine, so that could explain why it still works for me.

@JAkerblom : How about you: Do you also don't have a gateway installed?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

JAkerblom
Frequent Visitor

@ImkeF, No I don't have a gateway installed. Didn't understand why I'd need one to make a specific API call. 

 

I see now that people need to specifically list folder contents and not just go for a specific file, which is what I did. First I thought it was the api method that couldn't provide that list results of folder contents, but when I tried to just do a command line curl call it worked just fine, i.e. the list_folder method can return the list of folder contents (but just not in PowerBI Desktop for some reason..). I tried all types of header parameters in the web.contents method to make it work, but I didn't succeed. Either I'm doing it wrong or it's just not possible. But it would be really strange if certain API calls just don't work.  

 

masplin
Impactful Individual
Impactful Individual

masplin
Impactful Individual
Impactful Individual

Ok so plan B. The out of the box method in power Query for querying a folder is create this big mess with sample files that is tremendusly confusing.  Is there a way to just have a single qury that does the job?

 

For example I want to query these 2 files in a dropbox folder. The first step I see this

 

Capture.PNG

 

However if i click the arrows on binary it sets off greating the sample file.

 

Capture.PNG

 

Can I get round this with some code similar to what Imke wrote for dropbox?  I assume it is a similar exercise but my M is too pooor to work it out 😞

 

Mike

masplin
Impactful Individual
Impactful Individual

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors