Power BI Gateway unable to connect to Excel workbook saved on SharePoint Online

[tbush]

Hi,

I can't set up an Excel Workbook saved on SharePoint Online as a data source with the Power BI Gateway:

M# code:

let
    Source = Excel.Workbook(Web.Contents("https://TENANTNAME.sharepoint.com/sites/path/to/workbook/WORKBOOKNAME.xlsx"), null, true),

This works fine in Power BI desktop.

However I can't pick Oath2 authentication in the Gateway setup for a Web data source. When trying with Windows Authentication instead I get the following error:

Status code:	400
Error Code:	DM_GWPipeline_Gateway_InvalidConnectionCredentials
The credentials provided for the Web source are invalid. 

When will Oath2 authentication be addded to the Gateway Web data source? Or, is there another way of consuming the workbook hosted on SharePoint?

[v-shex-msft · Accepted solution]

Hi @tbush,

It seems like we are in the wrong direction, Excel files in Sharepoint Online not need to use gateway to refresh.

You can use the "connect directly" mode to link the file and modify the credentials to enable the refresh.(Setting -> Datasets)

If you want to know more about power bi data refresh, you can take a look at below article:

Data sources for Power BI service

Data refresh in Power BI

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @tbush,

You can try to use SharePoint.Files function to get the datasource:

let
    Source = SharePoint.Files(https://TENANTNAME.sharepoint.com/sites/path/to/workbook/WORKBOOKNAME.xlsx")

Regards,

Xiaoxin Sheng

[tbush]

Hi Xiaoxin,

I changed my query to use SharePoint.Files and I now have the option for oath authentication in the Power BI Gateways > Add Data Source screen.

However I'm now getting the following error:

Unable to connect: We encountered an error while trying to connect to https://COMPANY.sharepoint.com/. Details: "We reached the data gateway, but the gateway can't access the on-premises data source."Hide details

Activity ID:	a48ad216-9baf-4894-b063-b717992b09c9
Request ID:	751c37a1-1b3f-a0ef-dc2b-173a814324da
Cluster URI:	https://wabi-australia-southeast-redirect.analysis.windows.net
Status code:	400
Error Code:	DM_GWPipeline_Gateway_DataSourceAccessError
Time:	Mon Jan 23 2017 10:04:48 GMT+1300 (New Zealand Daylight Time)
Version:	13.0.1700.1015

The Power BI Gateways > Add Data Source screen didn't show a window asking me to sign into Office 365. Does it attempt to connect to SharePoint using the account I'm signed into PowerBI with?

How can we troubleshoot it further, can you lookup the error the SharePoint server is giving in the logs and come back to me?

I am trying to connect the Gateway to SharePoint Online for Office 365, not an on-premesis SharePoint site.

[v-shex-msft · Accepted solution]

Hi @tbush,

It seems like we are in the wrong direction, Excel files in Sharepoint Online not need to use gateway to refresh.

You can use the "connect directly" mode to link the file and modify the credentials to enable the refresh.(Setting -> Datasets)

If you want to know more about power bi data refresh, you can take a look at below article:

Data sources for Power BI service

Data refresh in Power BI

Regards,

Xiaoxin Sheng

[tbush · Accepted solution]

Thank you for the reply, my use case involves mashing up this Excel workbook with on premesis data from SQL Server in the same data model.

So we are buggered and can't use the enterprise gateway at the moment because this feature is not supported. I have voted for it to be added.

[jodur]

I have the same issue. In my model (Dataset) i use a combination of data from our Firebird SQL server and a excel file that is stored in on a sharepoint site. In this use case we are not able to use the combination. The only solution for now that works, is to store the excel file on our local network.