Connect Power BI to files in BOX

新規投稿

コメント

37件のコメント

  • 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
    コメントアクション パーマリンク
  • jnickell

    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
    コメントアクション パーマリンク
  • 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
    コメントアクション パーマリンク
  • 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
    コメントアクション パーマリンク
  • acips

    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?!

    0
    コメントアクション パーマリンク
  • jnickell

    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)

    0
    コメントアクション パーマリンク
  • acips

    Thank you! I will definitely look more deeply into the app. Really appreciate your help!

    0
    コメントアクション パーマリンク

サインインしてコメントを残してください。