Credential Error for Excel File hosted on One Drive

[THEG72]

Hi Power BI Support Team,

I am getting a credential errors on an Excel files stored on One Drive when i try to add the data source to the gateway.

Here is my detailed environment:
Windows 10 Pro,Version 10.0.1734 Build
Office 365 Business Premium
Power BI Professional

When i log into the laptop i simply enter my email address such as garry@xyz.com. I have a personal domain name associated with subscription.

My local PC where the gateway is installed shows the following in regards to the system information

Local PC

The Excel file is located in the root directory of the onedrive.

When i open the Excel file in the desktop App from the one drive online and copy the url from within Excel it returns the result below:

https://xyz-my.sharepoint.com/personal/myfirstname_xyz_com/Documents/MyTestSpreadsheet.xlsx?web=1

I understand i need to remove the "?web=1" from the url stated above when entering the path for the Web data source.

I tested the url by pasting it to a browser and it opens the file in Excel on line with the ?web=1 or without in Excel desktop app.

Note i am not using the File Data source but Web as the data source.

Data Source Add screen
What is the correct syntax for entering  the Windows User Name for gateway laptop NOT in a business network domain and without an on premise active directory?

I have tried several options and referred to previous comments in this area but I cannot get this to work and get credential error messages as shown below:

Unable to connect:
We encountered an error while trying to connect to .
Details: "We could not register this data source for any gateway instances within this cluster.
Please find more details below about specific errors for each gateway instance."Hide details
Activity ID:
5a804452-9dc7-4031-902f-132fbdf1baae
Request ID:
6487d495-7748-8d53-17bd-a6c1799ba603
Cluster URI:
https://wabi-australia-southeast-redirect.analysis.windows.net
Status code:
400
Error Code:
DMTS_PublishDatasourceToClusterErrorCode
Time:
Fri Jul 19 2019 13:16:51 GMT+0800 (W. Australia Standard Time)
Version:
13.0.10117.179
MyGATEWAY:
Invalid connection credentials.
Underlying error code:
-2147467259
Underlying error message:
The credentials provided for the Web source are invalid. (Source at https://xyz-my.sharepoint.com/personal/myfirstname_xyz_com/Documents/MyTestSpreadsheet.xlsx.)
DM_ErrorDetailNameCode_UnderlyingHResult:
-2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind:
Web
Microsoft.Data.Mashup.CredentialError.DataSourcePath:
https://xyz-my.sharepoint.com/personal/myfirstname_xyz_com/Documents/MyTestSpreadsheet.xlsx
Microsoft.Data.Mashup.CredentialError.Reason:
AccessForbidden
Microsoft.Data.Mashup.MashupSecurityException.DataSources:
[{"kind":"Web","path":"https://xyz-my.sharepoint.com/personal/myfirstname_xyz_com/Documents/MyTestSpreadsheet.xlsx"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:
AccessForbidden

Tried various versions of the username but no luck, appreciate any other options to try and get this working!

A one drive connector would be real useful.

[THEG72 · Accepted solution]

I ended up solving this by making sure the Data source credentials were set in the Power BI file before uploading.

Selecting Data Source Setting as organistational will then give you the OAUTH 2 option in the data source Add screen in the service.

[Anonymous]

Hello,

we started receiving this error. Everything was working fine for about 1 year, no changes in system nothiing. I even changed credentials in report and published it again, autorefresh just doesnt work, connection to Gateway and datasources is showing as its ok... very weird:

Underlying error code-2147467259 Table: B2B.

Underlying error messageThe key didn't match any rows in the table.

DM_ErrorDetailNameCode_UnderlyingHResult-2147467259

Microsoft.Data.Mashup.ValueError.Key[Schema = "dbo", Item = "B2Breport"]

Microsoft.Data.Mashup.ValueError.ReasonExpression.Error

Cluster URIWABI-WEST-EUROPE-redirect.analysis.windows.net

Activity ID8a71bee7-d489-4d96-ae04-94f44dd09cf4

Request IDc38dc306-3dc0-868b-77e5-bd61955b8def

Time2019-07-19 10:35:07Z

[THEG72]

@Anonymous 

Sounds like its the table data with the problem B2B table..."Underlying error messageThe key didn't match any rows in the table."..I dont see its a credential error from the error message you submitted.

Have your checked the columns of your B2B table data in the query editor for errors? I would step through each step of the transformations in your queries to see if its causing an issue. A sample image of the table data would be useful.

Perhaps submit more information about the data source so we can trouble shoot it.