Dropbox API v2, as API1 has been turned off

[masplin]

Hi

I have been linking severla single files stored on dropbox using API! whiuch seems to have bene disabled last night. I think there are a lot of us in the same boat.  Can someone say what the equivalent code is for API2?  I tried change the path to "https://api.dropboxapi.com/2/files/auto/DataModel/SourceFiles/" but that just errors.  My files are just sitting in a normal dropbox folder as can't us an App folder as can't be shared. 

The code i was using was

let
    Source = Excel.Workbook(Web.Contents(DropboxSourcePath & "Inputs.xlsx" & Token), null, true),
    Centre_Table = Source{[Item="Centre",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Centre_Table,{{"Opening Date", type date}})
in
    #"Changed Type"

Where  Dropboxsourcepath

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

and token

let
    Source = "?access_token=xxxxxxxxxxxxxxxxxxxxxxx"
in
    Source

Grateful any advice as whole company reporting has collapsed

Thanks

Mike

[v-yuezhe-msft]

@masplin,

Do mask sensitive data before uploading scripts or screenshots.

In your scenario, add a new blank query in Power BI Desktop, then paste the following code into the Advanced Editor of your query.

let
    Source = Excel.Workbook(Web.Contents("https://content.dropboxapi.com/2/files/download", [Headers=[Authorization="Bearer <your access token>", #"Dropbox-API-Arg"="{""path"": ""/folder/path/filename.xlsx""}"]]), null, true)
in
    Source

And I would commend you firstly test that if your API returns correct result in DropBox API Explorer.


Regards,
Lydia

[masplin]

Hi Lydia

I'm obviously doing somethnig stupid.  the path to my file is U:\Dropbox\DataModel\SourceFiles\Input.xlsx

In API explorer I got this

Can you tell me where i've gone wrong please?

Thanks

Mike

[granthworth]

Hey @masplin

I am still working on getting ours resolved also. I have not gotten around the issue completely yet but I was able to get at least a portion of Lydia's fix to work. See below for my info. Hope this helps.

[masplin]

let an "s" of the file name Duh!!!!

Brilliant

[masplin]

Ok so I got a repsonse from the API explorer uses this token and path, and can download the file however i then put this code in Power Query and get an error "unable to conenct to the remote server"? Any idea?

let
    Source = Excel.Workbook(Web.Contents("https://content.dropboxapi.com/2/files/download", [Headers=[Authorization="Bearer lkPfa4vOd-AAAAAAAAAAXpFuGV2UKJk0D3WGQtPjNeIeZNwdopvcaSs_y3gguRYY", #"Dropbox-API-Arg"="{""path"": ""/DataModel/SourceFiles/Inputs.xlsx""}"]]), null, true)
in
    Source

[granthworth]

That, I am not sure about. 

I was able to finally get our entire system up and running again. I updated ONLY the first line of code in each query under the Advanced Editor (only the code highlighted in blue below) as shown below. I left all other code the same and all queries now return with no error.

[masplin]

Must have been an issue on my workcomputer as now works fine.

Last question as you can see in my old API1 code I used 2 subset queries one ot hold the Token and one to hold the path. Made it easier if either changed. 

I tried this code by doesn't seem to work unless you write both out in full

let
    Source = Excel.Workbook(Web.Contents("https://content.dropboxapi.com/2/files/download", [Headers=[Authorization="Bearer Token", #"Dropbox-API-Arg"="{""path"": ""DropboxSourcePath & Inputs.xlsx""}"]]), null, true)
in
    Source

Where DropboxSourcePath is 

let
    Source = "/DataModel/SourceFiles/"
in
    Source

 and Token is

let
    Source = "lkPfa4vOd-AAAAAAAAAAKjmGh93HWPeYF-CY0LSTVss5JF53HOGK46xxk"
in
    Source

Assume i have an error in my syntax?

Mike

[Dindo]

If you're running with on premise gateway, how would you add this data source?

In V1 it's easy since it reads as a web url source. Anyone has luck trying this out?

[masplin]

The code above works fine except you have to put the exact tokena nd path in the source line.  i managed to get it to read the table.

I'm just hoping to be able to use some sub queries ot make it look neater and easier to manage

[Anonymous]

your code (below) works only on PBI Desktop.

once it is published, it gets credential error on PBI Service.

is there any workaround to make it work and refresh on PBI Service?

let
Source = Excel.Workbook(Web.Contents("https://content.dropboxapi.com/2/files/download", [Headers=[Authorization="Bearer mytoken", #"Dropbox-API-Arg"="{""path"": ""/myfolder/myfile.xlsx""}"]]), null, true)

in
#"Source"

[granthworth]

You are exactly right. I just went to the service to change our refresh schedule and noticed the error. I am looking into it now and will report back if I find a solution. Sorry about that!

[masplin]

Hmm wierd as works fine for me on the srvice. I updated al lmy sources in desktop, republished and it just worked? did even touch the credentials.

[Anonymous]

yeah, I know. I suspect there is an issue in my Brazilian server from PBI Service, as i have another dropbox project that I know works on US east (Virginia) but does not work here.

where is your PBI Service data being stored? USA? other place?

which version are you using?

I am under:

Version 13.0.2597.188
Activity ID nah
TimeTue Oct 03 2017 17:34:25 GMT-0300 (Hora oficial do Brasil)
Your data is stored inBrazil South (Sao Paulo State)

[masplin]

where do you find that info? I'm in UK so guess some european host

[masplin]

north europe (ireland)

[Anonymous]

at the upper right corner, click "?" button, and go to About Power BI.

[granthworth]

My info:

Version13.0.2597.188

Activity ID......
TimeTue Oct 03 2017 15:54:20 GMT-0500 (Central Daylight Time)
Your data is stored inNorth Central US (Illinois)

Error screenshots from service:

[masplin]

Sorry I have no idea as not really a techy. 

[masplin]

seems  i have misled everyone. I rechecked and doesnt seem I had applied the source changes in my model. i just did republished and now have the same error as you guys. That's a little frustrating to say the least. 

Mike

[v-yuezhe-msft]

@masplin,

I get same error as yours when enter credential for Dropbox in Power BI Service, the workaround is to republish PBIX file each time you have refresh.

Meanwhile, I would recommend you vote up the following idea that add Dropbox connector in Power BI.
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6663925-dropbox

In addition, do mask sensitive data such as access token before uploading your pictures and posting your replies.

Regards,
Lydia