How to add Excel file to a Standard data gateway in Power BI Service

[Ibrahim_shaik]

Hello Power BI Community,

I have a report connected to sql database in cloud and three excel files in local server.

I have configured standard data gateway and i need the data to auto refresh, I configured the gateway and it's online and I have mapped the report to the gateway connection as i have three excel files as well that i am trying to add, which are in my server to add these three files to the gateway i need to create a connection and in that I have entered the device admin credentials but i get an Error as Unable to create connection "Invalid Connection credentials"

Details: The credentials provided for the file source are invalid(source at ........)

status code: 400

Please suggest a solution to fix this issue.

Thanks & Regards,

Ibrahim.

[v-kpoloju-msft · Accepted solution]

Hi @Ibrahim_shaik,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @lbendlin, for your inputs on this issue.

After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

SharePoint and OneDrive utilize OAuth2 authentication, which means they do not require a gateway. By moving your Excel files there, you can use the SharePoint Folder connector in Power BI, ensuring the refresh will work without the Data Gateway.

Local Excel files stored on a server require Windows authentication through the Standard Data Gateway. Given the "Invalid Connection Credentials" error, please verify: You are using a UNC path (\\ServerName\SharedFolder\File.xlsx) instead of a local C:\ drive path. The Windows user specified in the gateway settings has access to the folder and file.

The gateway is online and correctly mapped under Manage Gateways in Power BI Service. Moving the Excel files to SharePoint/OneDrive could be the simplest solution to avoid gateway-related issues.

Kindly refer to the below following link for more information:

Power BI Desktop OneDrive and SharePoint integration - Power BI | Microsoft Learn

I hope this could resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[lbendlin]

Place your Excel files onto a OneDrive or SharePoint.  Use the SharePoint Folder connector, NOT the Web/Excel connector.   No gateway will be required.

[Ibrahim_shaik]

If i place my excel files in onedrive then i don't have to add the excel file to data gateway? as i have a data gateway for the sql database to auto refresh the data at regular intervals.

[lbendlin]

Correct. No gateway required unless you make the mistake to attempt and merge the on-prem data source and the cloud data source in Power Query.

[Ibrahim_shaik]

I have a report which is connected to a Cloud SQL database and few excel files and i need the report to auto refresh for that i have installed a standard data gateway and created a SQL connection to the gateway and I also have to create a excel connection to the gateway for that windows authentication needs to be provided and when I enter the windows username and password I get an error as "the credentials provided for the source file are invalid"

[lbendlin]

SharePoint uses OAUTH2 authentication, not Windows authentication.