Connecting to data source hosted on Dropbox

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

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.

Folder need to start with a slash:

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

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))
*/

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

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)

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)

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?

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 

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.

Thx for reporting this back here!

masplin, IT WORKED!!! thanks a lot!

No more credential errors, dropbox api v2 and PBI Service working as they should!

great!

Out of interest what were you trying to do?  If you just put the path to the dropbox folder like normal then it shoudl stil lwork as long as the path is the one on which your gatewayis running.  Theo nly advantage of the method i put is you can open desktop on various machines, where dropbox will have differnet paths, and still refresh them locally.

I need to connect dropbox cloud directly to PBI cloud. This way, me and my team will be able to use only the PBI Service to see all data that was edited by everyone.
example, user A may change all files 1, 2 and 3, also user B and C may change all 3 files.
once dropbox gets them updated, all 3 users will use only the PBI Service to see the updated changes.

now comes my new question: how to make it open full folders, intead of individual files? something like folder option on PBI Desktop.

ah when you work that out let me know!!!

Have you tried Imke solution where you create a folder under Apps?  That's what i was going to try next.

didn´t tried that yet... but I will try it soon.

thanks for your reply.

only today I got time to test it, and I couldn´t make it work.

I found the list_folder under API Explorer, got the HTML code, but I cannot insert it in the other web queries from the other posts.

could you please send an example of your working code?

@@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?

Sorry Anonymous! The example I used to implement this was a company production report. I'd have to make a test example, but I can do it with a little spare time today or tomorrow if you're still stuck. 

It is api v2. My instructions tell you to get the access token via the API Explorer link on the home page of dropbox.com/developers. Ctrl+F your way to "list_folder" and click on Get Token and follow the instructions to get a token for what you need. In my situation I have both a personal account and a company one, so I have to choose before I can generate a token. 

After that you have the query step in powerbi desktop. This line is all you need and then you can go on and navigate to the excel sheets you're interested in. Nothing else. 

= Excel.Workbook(Web.Contents("https://api-content.dropbox.com/1/files/auto/<PATH FROM DROPBOX ROOT TO EXCEL OF INTEREST>/<EXCEL FILENAME>?access_token=<ACCESS TOKEN GENERATED FOR THE DROPBOX ROOT YOU'RE IN>"), null, true)"

So in context, my M code looks like:

let
    Source = Excel.Workbook(Web.Contents("https://api-content.dropbox.com/1/files/auto/<PATH FROM DROPBOX ROOT TO EXCEL OF INTEREST>/<EXCEL FILENAME>?access_token=<ACCESS TOKEN GENERATED FOR THE DROPBOX ROOT YOU'RE IN>"), null, true), 
    Assignments_Sheet = Source{[Item="Assignments",Kind="Sheet"]}[Data], 
    #"Promoted Headers" = Table.PromoteHeaders(Assignments_Sheet, [PromoteAllScalars=true]),
    and so on...

JAkerblom, I really apreciate if you could test the list_folder command for me.

once I can connect to Dropbox using my token and view all files, I can handle them myself. but first I need to connect to the files and view them inside PBI query editor.

using web query or M language, I am having issue to make it work with my token and the supposed-to-be link below.

https://api.dropboxapi.com/2/files/list_folder

as I learned in Dropbox,

API v1 is: api-content.dropbox.com/1/etc

API v2 is: api.dropboxapi.com/2/etc

This is why I believe your code is using the old v1 API.

https://www.dropbox.com/developers/reference/migration-guide

Follow these images below.

And second, you're not supposed to use that link as you implied. Use the one I mentioned, and use it the way I described and all will be alright.

https://api-content.dropbox.com/1/files/auto/<Path to file>?access_token=<token>

yes, I followed these steps correctly, but this link only works when a file name is provided.

I created a new token for the new command list_folder.

I don´t want to load a single file, I need to load the contents of a folder, and view all files inside the folder.

I tried many times, many different codes. I got a lot of errors each test: 400, 404 and 405.

And so far nothing is loaded.

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 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

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.