Connecting to data source hosted on Dropbox

[JohnSwid]

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!

[Anonymous]

I already voted. Thanks for the link.

So far, the only way to make PBI Service work is loading single files using the code from user Masplin.

3rd comment on 3rd page of this same topic.

http://community.powerbi.com/t5/Integrations-with-Files-and/Connecting-to-data-source-hosted-on-Drop...

[masplin]

The code is fine to use within excel to query a dropbox folder. If you put that same query in PBI desktop and then publish to Power BI Service it fails.

I just spend hours getting it working in Excel, replaced everythnig in PBI and then had to reverse it all out!!!  Very frustrating.

Mike 

[ImkeF]

Yes, there are many things that don't work in the service unfortunately.

[masplin]

I'll check to see if anyone raised this to be voted on and if not add it.

[Anonymous]

Thanks a lot for your code Imke!
But would you have a compact version of it? without the function parameters...

Because your code is a function, the Gateway does not work. I get credential errors.

I am loking for just the direct link, something like the first comment on this idea.

But I couldn´t make it work this example in the comments...
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6663925-dropbox (opens in new tab)

[ImkeF]

Not sure if I understood you correctly (and very much doubt that that's the reason), but here you go: Function converted to "ordinary" query:

//let Source=
//(token as text, optional folder as text) => 

let
token = "<<FillInYourTokenHere>>",
folder = null, // replace null with folder name if you want to use it
    data = [    path= if folder = null then "" else folder,
                recursive=false,
                include_media_info=false,
                include_deleted=false,
                include_has_explicit_shared_members=false
],
    header = [  #"Authorization"="Bearer "&token,
                #"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),
    entries = out[entries],
    ToTable = Table.FromList(entries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandProperties = Table.ExpandRecordColumn(ToTable, "Column1", {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}, {".tag", "name", "path_lower", "path_display", "id", "client_modified", "server_modified", "rev", "size", "content_hash"}),
    RetrieveContent = Table.AddColumn(ExpandProperties, "File", each Web.Contents("https://content.dropboxapi.com/2/files/download",[
    Headers=[#"Dropbox-API-Arg"="{""path"":"""&[path_display]&"""}", #"Authorization" = header[Authorization]]]))

in
RetrieveContent 

/*
,documentation = [	
Documentation.Name =  "	fnDropbox.Folder
", Documentation.Description = "	Returns a table with contents from your selected Dropbox folder
" , Documentation.LongDescription = "	Returns a table with contents from your selected Dropbox folder. The optional field 'folder' allows you to access sub-folders within the main folder.
", Documentation.Category = "	Accessing data functions
", Documentation.Source = "	local
", Documentation.Author = "	Imke Feldmann: www.TheBIccountant.com
", Documentation.Examples = {[Description =  "	
" , Code = "	Check this blogpost explaining how it works: http://wp.me/p6lgsG-AA
 ", Result = "	
"]}]	
 in	
  Value.ReplaceType(Source, Value.ReplaceMetadata(Value.Type(Source), documentation))
*/

[Anonymous]

yes Imke! thanks for the ordinary query! it was exactly what I wanted!
but somehow, there is still error on gateway...

I need to make a Power BI project where the data comes from Dropbox cloud straight to Power BI cloud (service).

the project needs to use only the token, so any other user may open the file hosted in dropbox´s cloud.

using both your codes, I get the exact same error on credentials.

for the sencond code, I started a brand new file on power BI, this way there are no leftovers from the first code.

I think this error is a limitation from the Power BI Service´s Gateway...

Maybe we need to put all parameters inside a single URL:

how it is now:

Web.Contents("https://api.dropboxapi.com/2/files/list_folder",[Content=Json.FromValue(data),Headers=header]),
out = Json.Document(response,1252)

how it might be to make it work:

Web.Contents("https://api.dropboxapi.com/2/files/list_folder/data/token",),
out = Json.Document(response,1252)

what do you think?

[ImkeF]

Sorry, but I have no experience with the data gateway.

You might consider posting that question in the Service-Forum or in the Integrations-forum: http://community.powerbi.com/t5/Integrations-with-Files-and/bd-p/power-bi-services 

[JAkerblom]

I'm having the same problem as the previous poster (DN). It can't connect anymore once it's published to the Power BI Service. 

It doesn't matter if it's your solution ImkeF or if I'm just doing a web connection through the string;

https://api-content.dropbox.com/1/files/auto/<path to file in app folder>?access_token=<access token>

It works fine locally and I can get the data source from both alternatives, but it's the published version that is erroneous.

Is this a scenario that they just haven't taken into account? That I'd actually like to publish my report to PowerBI and be able to refresh the report from there? It just seems silly to me.

[Anonymous]

I opened a support ticket, and yes they confirmed this is a limitation. It is not possible to make PBI cloud connect directly to Dropbox cloud. And I really believe that any other cloud will not connect directly to PBI cloud (PBI Service)
As an alternative, they suggested I use OneDrive, and now I need to start new tests using OneDrive.

This is what they suggest:

Keeping your files on OneDrive, or SharePoint Online, and connecting to them from Power BI, provides a great amount of flexibility. But with all that flexibility, it also makes it one of the most challenging to understand. Scheduled refresh for files stored in OneDrive, or SharePoint Online, are different from package refresh. You can learn more in the Types of refresh section.

Reference article: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/

Package refresh

This synchronizes your Power BI Desktop, or Excel, file between the Power BI service and OneDrive, or SharePoint Online. This does not pull data from the original data source. The dataset in Power BI will only be updated with what is in the file within OneDrive, or SharePoint Online.

Reference article: https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/#types-of-refresh

[ImkeF]

Thx for reporting this back here!

[masplin]

This is not true!!!!

I have successful connected my desktop model to an anonnymous dropbox link and it refreshes absolutely fine on PBI servce. OneDrive is an absolute disaster as a cloud storage system, it works for Power bI but is garbagge for anything else.

The issue I have is where my query is doing a folder query so I need ot try Imke solution

For example I have this file Inputs.xlsx which sits in Dropbox\DataModels\SourceFiles.

firstly create a blank query to set the path to the file

DropboxSourcePath is

let
Source = "https://api-content.dropbox.com/1/files/auto/DataModel/SourceFiles/"
in
Source

Secondly go onto dropbox online and into developer section as Imke described ot create an app and get a token.  In my case I have tried her folder app so I just got a token for the whole of dropbox.

Token is

let
Source = "?access_token=lkPfa4vOd-AAAAAAAAAAKjmGh93HWPeYFfffdfffdfdggf-CY0LSTVss5JF53HOGK46xxkWsQzhj-"
in
Source

Then your query becomes. Note now web.contents instead of file.contents and if you have any path its "/" not "\"

let
Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "Inputs.xlsx" & Token), null, true),
Seasonality_Table = Source{[Item="Seasonality",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Seasonality_Table,{{"Month No", Int64.Type}, {"Month", type text}, {"Car Count Seasonality", type number}, {"GTV Seasonality", type number}})
in
#"Changed Type"

So I can refresh all conenctions on desktop and when I publish to PBI Service I see under credential web for each of these dropbox links. Edit conenction and chose annonymous.

Mike

[LSsoftware]

Hi @masplin,

I'm quite new on Power BI and doesn't have a developer background. I've noticed the solution you provided for this thread and i would like to understand how it work.

My problem is that i got tonnes of photos stored on Dropbox. I've copied the URLs into an Excel Spreadsheet and loaded in Power BI. The photos doesn't show up and i assumed it's because you need to connect Dropbox with Power BI first in order for Dropbox to give you the permission. Would you mind teaching me how it can be done, please? It would be great if we can chat on email as this is more appropriate. Here's my email - shen.tan@leapsheep.biz

Regards,

Shen Tan

[Lysogenic]

Reposting code as last version had wrong url! 🙂

let

DropboxSourcePath = "https://api-content.dropbox.com/1/files/auto/",

Token = "?access_token=[token]",

Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "delivery_recieved.xls" & Token), null, true)

in

Source

[Lysogenic]

Hi there,

Trying to follow this code to get my PowerBi to refresh using web service but cannot seem to replicate.

In a blank query i am putting the following, is this right?

let

DropboxSourcePath = "https://content.dropboxapi.com/2/files/download/",

Token = "?access_token=[Token]",

Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "delivery_recieved.xls" & Token), null, true)

in

Source

Any help appreciated!

[Lysogenic]

Hi there,

Trying to follow this code to get my PowerBi to refresh using web service but cannot seem to replicate.

In a blank query i am putting the following, is this right?

let

DropboxSourcePath = "https://content.dropboxapi.com/2/files/download/",

Token = "?access_token=[Token]",

Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "delivery_recieved.xls" & Token), null, true)

in

Source

Any help appreciated!

[JAkerblom]

I know what the catch is now. The devil is in the details.

At first I didn't at all know that there was a way to get any other access token string than to create an app and generate it from there. But when I saw your post and the bit with "In my case I have tried her folder app so I just got a token for the whole of dropbox.", I realized there had to be another way and was a bit irritated that the solution to this haven't been emphazised in any of the threads about this topic. Just as if it was obvious or something =). 

Once in dropbox.com/developers, go to API Explorer. For the action we are trying to do here is the list_folder command, so click on that from the margin. Then you can generate an access token for the whole of dropbox. You can even pick personal or organizational account when generating. In this case you don't need to have a function and so on. Just do the Web query, insert the url string as detailed in the previous responses and you'll then be able to pick the excel sheets as tables. 

It worked just fine when publishing as well, just like masplin said. (Y)

[EllenG]

Thank you for clarifying.  PowerBI's limitations and complexity are a huge time-sink. For anyone listening - NONE Of these issues exists with Tableau!!!! No DAX, no Garden, no fumbling through years of community frustration to find solutions.  

[EllenG]

This is an excellent how-to - huge time saver!  Wish I had seen this right away.  Microsoft, are you listening?

[masplin]

Hi I'm trying your suggestion.

Im on API Explorer page and clicked list_folder

I've done get token and in the path put  DataModel/3rd Party/WCFMC which is the folder where the files are

When I click submit call i get

Error: 400

Error in call to API function "files/list_folder": request body: path: 'DataModel/3rd Party Bookings/WCFMC' did not match pattern '(/(.|[\r\n])*)?|id:.*|(ns:[0-9]+(/.*)?)'

So I'm no further being ablew to access my folder on dropbox?  

What exactyl did you do to getthis to work please?

Mike

[Anonymous]

@@JAkerblo, are you using api v1 or v2?

I cannot insert the list_folder code in the other web queries from the other posts.

Could you please send an example of your working code?