Box Status

Connect Power BI to files in BOX

Follow
New post
Filo

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.

0

Comments

34 comments

Date Votes
  • Soong Panya

    I've tried everything but I still can't get data from box to power BI. Does anyone have a working solution please? 

    0
    Comment actions Permalink
  • jnickell
    • Edited

    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?

    0
    Comment actions Permalink
  • acips

    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!

    0
    Comment actions Permalink
  • jnickell

    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
        Source

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

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

    Hope this helps

    0
    Comment actions Permalink

Please sign in to leave a comment.