Automatic refresh with online Excel

[eduardosilvin3]

Hello guys, I have a report with several sources and one is an Excel in a shared folder, I got the data as Web source and workd correctly. But, when I upload the report to PBI Service, it requires the credentials of all the sources for the automated refresh.

I tried all the options (it is a protected file by the company), I tried the options and nothing is working. Any idea?

Cheers!

[Shravan133 · Accepted solution]

Here are some things you can try:

1. Correct Data Source Credentials in Power BI Service:

   • Go to the dataset settings in Power BI Service.
   • Under "Data source credentials," locate the Web source corresponding to your Excel file.
   • Choose "Edit credentials" and select the appropriate authentication method (Organizational, OAuth2, etc.).
   • Make sure the credentials match what’s required by your company’s protected file.

2. Data Gateway Configuration:

   • If your Excel file resides on a local network or is protected, you may need to use an On-premises Data Gateway.
   • Install and configure the Data Gateway to access the shared folder and file, ensuring the gateway has access to the file path or Web location where the file is hosted.

3. Workaround: Using OneDrive or SharePoint:

   • If you’re accessing the file from a shared folder, consider moving the Excel file to a SharePoint Online or OneDrive for Business folder. These services work seamlessly with Power BI for both Web and file connections.
   • Reconnect your Power BI report to the file in SharePoint/OneDrive using the "Web" or "SharePoint Folder" connector, and the refresh should work without complex credential issues.

4. Ensure Web Access Permissions:

   • Make sure the user account used for the automated refresh has the necessary permissions to access the file through the Web source. You may need to check with your IT team to ensure the correct setup.

[eduardosilvin3 · Accepted solution]

Steps from Shravan133 are correct but no for my case.

My mistake, in case any one have it, I clicked "Share" and took that URL, but that is not correct, the correct way is: 

Go to the Excel in desktop > file> info > copy path > use it as source in PBI Desktop (delete the last part of the URL, 'web1...' from the end until the extension of the file, keeping the file extension as the last part of the URL) --- in PBI Service> seetings of the dataset > edit credentials > after add the gateway use the OAuth2 > sign in in the external Window > connenct

[Shravan133 · Accepted solution]

Here are some things you can try:

1. Correct Data Source Credentials in Power BI Service:

   • Go to the dataset settings in Power BI Service.
   • Under "Data source credentials," locate the Web source corresponding to your Excel file.
   • Choose "Edit credentials" and select the appropriate authentication method (Organizational, OAuth2, etc.).
   • Make sure the credentials match what’s required by your company’s protected file.

2. Data Gateway Configuration:

   • If your Excel file resides on a local network or is protected, you may need to use an On-premises Data Gateway.
   • Install and configure the Data Gateway to access the shared folder and file, ensuring the gateway has access to the file path or Web location where the file is hosted.

3. Workaround: Using OneDrive or SharePoint:

   • If you’re accessing the file from a shared folder, consider moving the Excel file to a SharePoint Online or OneDrive for Business folder. These services work seamlessly with Power BI for both Web and file connections.
   • Reconnect your Power BI report to the file in SharePoint/OneDrive using the "Web" or "SharePoint Folder" connector, and the refresh should work without complex credential issues.

4. Ensure Web Access Permissions:

   • Make sure the user account used for the automated refresh has the necessary permissions to access the file through the Web source. You may need to check with your IT team to ensure the correct setup.