The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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
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.
@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/3
Thank you for your understanding.
Regards,
Charlie Liao
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.
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
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.
V1 is no longer available though. Has anyone able to fix this?
I reached dropbox support, and the guy explained something that is really confusing:
API v1 is no longer available.
API v2 is the only option today.
Authentication OAUTH1 and OAUTH2 are both still working, and there is no relation between API version or OAUTH version.
It is possible to use APIv2 using OAUTH1, and now I need to update my code from the previous post...
FYI, @v-yuezhe-msft has the solution in the thread below. I have also attached a screenshot of my advanced query editor window with the fix in place (I ONLY edited the single line of code highlighted in blue in all of my queries to get them working again with v2).
the solution code (below) from the other post 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"
I have this same problem, and I am looking for a solution; I also used the function proposed by Imke Feldman and it worked perfectly in Power BI Desktop, however, I can not authenticate the source to schedule automatic updating.
If another function is required to allow authentication, I don't understand where this function should be inserted.
I opened a support ticket 5 months ago, still no solution. Every month they send my issue to another engineer, but no one helped so far. and sincerely, and I think they never will, as dropbox is rival to onedrive... sad.
This is driving me crazy.
Anyone found a solution to this?
Hey @Anonymous, any luck with getting things working with v2 this morning? Our entire Power BI system is not working at the moment due to this issue as I have everything setup through the "https://api-content.dropbox.com/1/files/auto" string as mentioned previously. I never received an email or any other notification from Dropbox about retiring v1 so I am not happy with them at the moment.
I have tried updating the URL syntax every way that I can think of according to 2 hours of research on the topic this morning and have not found a solution....
Yup all our stuff just blew up. Thisis really annoying as seems no one has na answer
Mike
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
42 | |
24 | |
23 | |
14 |