Forum Discussion
Dropbox API v2, gateway credential error
I have to connect the Dropbox cloud straight to Power BI cloud (service).
Using the code from this post, I can load data to Power BI Desktop (thanks to ImkeF), but I get credential error when trying to refresh the data on Power BI Service.
The code below works great on Power BI Desktop, but I need to be able to refresh data from inside Power BI Service.
Do I need to use another code?
Any tip on how to get correct credentials on gateway?
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
- Anonymous9 years ago
Below is the correct answer using API v1 and OAUTH1 from user Masplin (Mike), not me.
But I tried this and it worked perfectly!
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
SourceSecondly 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
SourceThen 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.
13 Replies
- v-caliao-msftMicrosoft Employee
Anonymous,
As you said in antoher link, it is not possible to make PBI cloud connect directly to Dropbox cloud.
http://community.powerbi.com/t5/Integrations-with-Files-and/Connecting-to-data-source-hosted-on-Dropbox/td-p/67946/page/3Thank you for your understanding.
Regards,
Charlie Liao
- AnonymousNot applicable
Actually, it is possible.
New updates on the other topic show how to use token to get file from Dropbox cloud direct into PBI Service.- AnonymousNot applicable
Below is the correct answer using API v1 and OAUTH1 from user Masplin (Mike), not me.
But I tried this and it worked perfectly!
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
SourceSecondly 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
SourceThen 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.