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.
-
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!
-
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.
-
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!
-
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!
-
The Power BI Community seems to be progressing.
https://community.powerbi.com/t5/Integrations-with-Files-and/box-com/td-p/119392
-
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
-
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.
RegardsBas
-
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
-
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
-
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.
-
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
-
Hi ,
Two things to check:
- 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.
- 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 -
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
-
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? -
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
SourceI'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. -
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?
-
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!
Please sign in to leave a comment.
Comments
37 comments