Connect Power BI to files in BOX
Hi all,
I would like to connect my excel file to some data sources stored in Box using Power BI.
I have tried to use Box for Office or the shared link in "get data from web", but I did not succeed.
Is there another way to connect Files stored in Box with Power BI?
Thank you.
-
Soong Panya have you tried my steps? I tried to add permalink path but that doesn't seem to work. Note that it does require setting up an app on the Box side to connect. My post is on page 1 near the end from about 3 yrs ago (2021)
This has been working for me pretty reliably for several years now. If you've followed my route, what errors are you getting?
-
Hi jnickell, thanks so much for sharing your code - much appreciated! However, I seem to be running into an issue I'm hoping you can help solve. I followed your instructions to a tee. I built the app based on the instructions you provided in the link you had included, and I also used your exact code (except for entering in my own client id, client secret, and enterprise id), but I keep getting the below "failed to get contents" error. Any advice you could provide?
Thank you!
-
The error message you're posting looks like the file # you provided is incorrect. Can you take the same file number and paste into the webapp and get to it?
i.e. https://<company>.box.com/file/<fileNumber>
Is the number you put there is a folder (instead of a file). You need to use a different endpoint to get to folder and it's contents.
For anyone who's finding this. I'll post my updated functions. They still rely on the Box app (that I referenced on Page 1). Just re-posting the code for Power Query, because I've made a few tweaks since I originally posted this.
///Get-BoxAuth
/// This is a function leveraged for authentication with Box API. Called by the others
let
Source = () => let
/// Authenticate
LoginURLRoot = "https://api.box.com/",
LoginURI = "oauth2/token",
PostBody = [
#"client_id" = "<yoursFromBoxApp>",
#"client_secret" = "<yoursFromBoxApp>",
#"grant_type" = "client_credentials",
#"box_subject_type" = "enterprise",
#"box_subject_id" = "<yoursFromBoxTenant>"
],
Header = [#"content-type"="application/x-www-form-urlencoded"],
PostBodyEncoded = Uri.BuildQueryString(PostBody),
out = Json.Document(Web.Contents(LoginURLRoot,[RelativePath=LoginURI,Headers=Header,Content=Text.ToBinary(PostBodyEncoded)])),
#"Converted to Table" = Record.ToTable(out),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "access_token")),
Value = #"Filtered Rows"{0}[Value]
in
Value
in
SourceThe next function retrieves a file
//// get-BoxFile-v2
let
Source = (FileID as text) => let
LoginURLRoot = "https://api.box.com/",
TargetURI = "2.0/files/",
/// Authenticate
Value = #"Get-BoxAuth"(),
//// Get the file
options =[
RelativePath = TargetURI & FileID & "/content",
Headers =[#"Authorization"="Bearer " & Value]
],
Source = Web.Contents(LoginURLRoot, options)
in
Source
in
SourceThis function is for getting Box Folder Items (has a helper function below). I believe the iterator for paging I found elsewhere online. Not remembering at the moment.
/// get-BoxFolderItems-v2
(FolderID as text) => let
// identify the number of queries we're going to need
AuthToken = #"Get-BoxAuth"(),
LoginURLRoot = "https://api.box.com/",
TargetURI = "2.0/folders/",
options =[
RelativePath = TargetURI & FolderID & "/items" ,
Headers =[#"Authorization"="Bearer " & AuthToken]
],
Source = Web.Contents(LoginURLRoot, options),
#"Imported JSON" = Json.Document(Source,1252),
#"Offset List" = List.Generate(()=> 0, each _ < #"Imported JSON"[total_count], each _ + #"Imported JSON"[limit] ),
#"QueryCount" = List.Count(#"Offset List"),
#"Converted to Table1" = Table.FromList(#"Offset List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Page"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Page", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Get Results", each
#"get-BoxFolderItemPage"( AuthToken, FolderID, [Page])
),
#"Expanded Get Results" = Table.ExpandListColumn(#"Added Custom", "Get Results"),
#"Expanded Get Results1" = Table.ExpandRecordColumn(#"Expanded Get Results", "Get Results", {"type", "id", "file_version", "sequence_id", "etag", "sha1", "name"}, {"type", "id", "file_version", "sequence_id", "etag", "sha1", "name"})
in
#"Expanded Get Results1"This is used to page through the results if you've got a folder with lots of items in it.
(AuthCode as text, FolderID as text, Offset as text) => let
// identify the number of queries we're going to need
LoginURLRoot = "https://api.box.com/",
TargetURI = "2.0/folders/",
options =[
RelativePath = TargetURI & FolderID & "/items?&offset=" & Offset ,
Headers =[#"Authorization"="Bearer " & AuthCode]
],
Source = Web.Contents(LoginURLRoot, options),
#"Imported JSON" = Json.Document(Source,1252),
entries = #"Imported JSON"[entries]
in
entriesHope this helps
-
jnickell Thanks SO much for taking the time to respond, including code to access folders (which will be my next step after solving the current issue).
The FileID I am using is for an individual spreadsheet file (not a folder). I am able to access the file just fine if I go to the normal URL (https://<company>.app.box.com/file.xxxxx), but the error message I get in PBI shows the failed URL as "https://api.box.com/2.0/files/xxxxx" per the code.\
If I edit the code to list the LoginURLRoot and TargetURI to match the normal URL I listed further above, there are no errors but it only navigates to my company's main landing page (see screenshot below).
Perhaps I am missing some specific settings in the app? What am I doing wrong?!
-
I would look at your app's setup or maybe permissions. The error message you're sharing is what I got if I intentionally supplied an incorrect file ID.
Have you double checked that the JWT app you created has access to the file?
I'm going to be out for a bit, but the code I provided was a copy/paste from my working code (minus the company specific info)
Please sign in to leave a comment.
Comments
37 comments