Connect Power BI to files in BOX

New post

Comments

37 comments

  • Howard

    Hi ,

     

    Thank you for your post, that's a really interesting idea! Could you tell us more about how you would use this at your company? Unfortunately this is not possible with excel, but this sounds like an interesting use case.

     

    The Box Community team shares selected conversations on the community back to other Box teams from time to time, including product feedback. However, if you'd like to provide direct feedback you can always submit a case to Box support.

     

    Thanks for your help and time in the Community!

    0
    Comment actions Permalink
  • Filo

    Hi Howard,

     

    If we are able to connect the excel file to a data source stored in Box, it would be possible for everyone to refresh a file from their end with new data. This is particularly useful when the final user is adding comments to the excel file and when the data source is often refreshed automatically in Box.

     

    Let me know if there are alternative solutions.

    0
    Comment actions Permalink
  • harborview

    This would be fantastic - why crickets?

    0
    Comment actions Permalink
  • harborview

    Still crickets???

    0
    Comment actions Permalink
  • scottdodds

    Hi ,

     

    Thanks for your post, and appreciate your interest on this idea!

     

    Currently our product team has not made any public announcements about this enhancement. But if you'd like to add your voice and support for it, the best way to do that is to submit a case to Box Support. That team can ensure your feedback is given to the right folks for consideration in our product roadmap. If you have additional use cases or examples to share of how it would improve your experience, that would also be helpful!

     

    Thanks for your time in the Community and we appreciate your help!

     

    0
    Comment actions Permalink
  • Basroozen

    Hi Filo,

    This is possible. For the file(s) you want to make accessible:

    1. Share them and set the sharing type to PEOPLE WITH THE LINK. 

    2. Click on the settings icon. Make sure the link expiry date is unchecked. Check the 'allow download' box. On the bottom you can find the link to the actual Excel file

    3. Go to PowerBi and create a WEB connection, not an Excel one. Paste the URL to the Box file and your connection is setup. 

    4. If you run into this issue where you get the message that there's something wrong with the HTML of the data source, check your connection settings.Do this by going to the ribbon and clicking the button below Edit Queries, then click data source settings. Make sure the data source permissions for Box are set to Windows.

     

    That's it!

    1
    Comment actions Permalink
  • zuludogm

    The Power BI Community seems to be progressing.

    https://community.powerbi.com/t5/Integrations-with-Files-and/box-com/td-p/119392

     

    0
    Comment actions Permalink
  • SurfinGuy

    Unfortunately that forum says to use OneDrive. 

    0
    Comment actions Permalink
  • MehulVora

    This worked for me.

    0
    Comment actions Permalink
  • sireeshapulipat

    The shared link opens the organizational sign in page inside Power BI. The link opens the file in the browser.

    0
    Comment actions Permalink
  • BalaA

    Hi ,

     

    I followed the below steps and getting the error.

    Details: "The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

     

    I using the windows authentication, please let me know if you have any solution to solve it.

     

    Thanks

    Bala

    0
    Comment actions Permalink
  • Basroozen

    Hi   and  ,

    Box indeed changed their shared URL functionality recently.

     

    1. Log in to Box. You should now be on a URL that's similar to https://companyname.ent.box.com/blabla

    2. Hit share for the file you want to pull into PowerBI

    3. Select enable shared link 

    4. Change the drop down from invited people only to people with the link, and click link settings

    5. Disable link expiry and copy the link you see in the Direct Link field

    6. So far things have been the same, but you might notice that the link in the direct link field starts with https://ent.box.com . You need to replace ent with the company name from step 1 so that it becomes https://companyname.box.com/blablabla

     

    This should do the trick.


    Regards

    Bas

    0
    Comment actions Permalink
  • chrissmith_fw

    This is pretty basic functionality and there are a multitude of use cases but why would we even need to post them? The benefits and advantages are obvious. 

    0
    Comment actions Permalink
  • elii

    Hi !

     

    Welcome to the community and thank you for your first post!

     

    Appreciate you sharing this feedback! Posting your use case with us can help us clearly understand how this type of functionality will specifically help you and your organization, and I would really encourage you to elaborate on the benefits that you see. We'd love to hear more about the advantages of connecting power BI to files in Box to get a better understanding of how this will help you!

     

    Thank you and hope to hear more from you soon!

    Emma

    Box Community Management Intern

    0
    Comment actions Permalink
  • cardef01

    Hi Bass

    Hi   and  , 

    I am following the steps,  the link that I get starts already with Company name

    https://companyname.box.com/blablabla

    I think that is good, but my concern is to set security to people with the link, that might represent a security issue in my company, is there any other option?

    Thanks

    Fabian

    0
    Comment actions Permalink
  • Basroozen

     Yes it does mean your data is public, and can get in to the wrong hands if someone would happen to land on that exact URL. My advice therefore would be to never store any personal identifiable information, and try and mask your information as much as possible by for instance using ID's instead of names. You can create a table in your PowerBI file that translates the IDs back to a name.

    0
    Comment actions Permalink
  • saloneesuhag

    I tried the above method but it takes me to Query Editor and all I see if the table structure but not the contents of the Excel workbook. How can I pull that information?

    0
    Comment actions Permalink
  • cheny134

    Hi  

     

    I followed your updated steps on 8/28 but still getting the error.

    Details: "The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

     

    Do you know what might be the problem? Should I use the link end with .xlsx? https://company.box.com/shared/static/xxxxxx.xlsx

     

    And to set the credential, I shall use windows and set the permission to file level, right? (eg. xlsx)

     

    Thanks,

    Yan

    0
    Comment actions Permalink
  • Basroozen

    Hi  ,

     

    Two things to check:

    1. Did you use Get Data from Web or Get Data from Excel? Even though you might be linking to an Excel file, you should use Get Data from Web.
    2. When sharing files, Box provides you two links actually. The first link is visible when you hit the Share button.en then click Enable Shared Link. That's the Box link to the file, and as you will see that link doesn't end with a file extension. You should NOT use this link. The link you want to use is showing up if you click on Link Settings next. This shows you the Direct Link, and in case it's an Excel file it'll show an Excel extension. That's the correct link you'll have to import to PowerBi.

    Regards
    Bas

    0
    Comment actions Permalink
  • cheny134

    Hi  ,

     

    Thanks for your quick reply.

    I tried again according what you just said.

    It is still not working and showing the same message.

    Is there something else that I may miss?

     

    Thanks

    Yan

    0
    Comment actions Permalink
  • chrissmith_fw

    Waste of bandwidth...Why bother?

    0
    Comment actions Permalink
  • SifraWestendorp

    Thanks  works perfectly!! 

    0
    Comment actions Permalink
  • rachael7817

    Kindly share the method you used to achieve this

    0
    Comment actions Permalink
  • Box Product Support

     wrote:

    Hi Bass

    Hi   and  , 

    I am following the steps,  the link that I get starts already with Company name

    https://companyname.box.com/blablabla

    I think that is good, but my concern is to set security to people with the link, that might represent a security issue in my company, is there any other option?

    Thanks

    Fabian


    is it working bro or not .. if yes then tell me i want to apply this too

    0
    Comment actions Permalink
  • Basroozen

    That's true. I haven't been able to find a solution for that yet I'm afraid. I just make sure I'm not putting anything confidential in there. And if I do have to work with confidential data I simply rename some fields so it's not possible to trace back what the content is about.

    0
    Comment actions Permalink
  • Jagan2

    Hi  

     
    Thanks for sharing the solution but when you change sharing setting and revert back to original state, this solution does not work. For ex: when u change to people in your company and revert it back to PEOPLE WITH THE LINK option. then power bi will throw an error while loading data.Is there any other way to connect to BOX from power bi?
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
    0
    Comment actions Permalink
  • jnickell

    While we wait on an official method.  I wanted to share what I just got to work. 

    This does require the creation of a OAuth 2.0 with Client Credentials Grant (Server Authentication) style app.

    Once you've got those the app setup and the Client ID, Client Secret, and Company ID (referencing here: https://box.dev/guides/authentication/jwt/without-sdk//#code-samples)

    You can use the query below to connect to a File on Box

    (FileID as text) => let
    /// Authenticate
    LoginURLRoot = "https://api.box.com/",
    LoginURI = "oauth2/token",
    TargetURI = "2.0/files/",
    PostBody = [
    #"client_id" = "<ClientIDFromApp>",
    #"client_secret" = "<ClientSecretFromApp>",
    #"grant_type" = "client_credentials",
    #"box_subject_type" = "enterprise",
    #"box_subject_id" = "<EnterpriseID>"
    ],
    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],
    //// Get the file
    options =[
    RelativePath = TargetURI & FileID & "/content",
    Headers =[#"Authorization"="Bearer " & Value]
    ],
    Source = Web.Contents(LoginURLRoot, options)
    in
    Source

    I'm sure this can be improved but I am just thrilled I actually got something working.
    Note that I have seen this "work" and then give me error messages and then work again w/o any changes. I'm guessing this has to do with API throttling, but I haven't got that far yet.
    --- Updated the PowerQuery to be a Function and to use RelativePath so that it can be used on the PowerBI Web Service.

    1
    Comment actions Permalink
  • Jesus Chaves Benito

    Good morning, 

    I am able to connect Power Bi with BOX and also the refresh of the data is working from my laptop in Power BI Desktop.

    However, when I publish the report to Power BI Service it is not possible to schedule the refresh. 

    Error message: "Scheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refresh."

    "Data Source Credentials" section is disabled in the "Settings" section of the Dataset in Power Bi Service, so I can not enter credentials to schedule the refresh. 

    Any Idea how to solve this?

    0
    Comment actions Permalink
  • John Nickell

    I'm using "anonymous" for a Dataflow connection for my purposes.

    0
    Comment actions Permalink
  • Muhan (Leo) Sun

    Hey guys, I was going through our chat records and realized that we haven't solved this issue for so many years. I just tried something and it worked. Here are the steps:

    1. Share the files and set the sharing type to "People with the link."

    2. Click on the settings icon. Make sure the link expiry date is unchecked and check the "allow download" box. On the bottom, you can find the link to the actual Excel file.

    3. Go to Power BI and create a web connection, not an Excel one. Paste the URL to the Box file, and your connection is set up. 

    Now, here's the key step:

    4. You need to copy the Direct link from the Shared Link Settings page and paste it into Power BI. When creating the web connection page, select "Advanced" and in the "HTTP request header parameters (optional)" section at the bottom, select the "Content-Type" parameter. In the input box to the right, enter the file format corresponding to your link, such as CSV, JSON, and so on. Then you're done!

    1
    Comment actions Permalink

Please sign in to leave a comment.