Excel data source functionality in Power BI

[Anonymous]

Hi all,

Wondered if anyone could clarify what I should expect to be able to do with Excel files uploaded to the Power BI office 365 Portal in relation to external data sources.

To put this into context we have recently locked down SQL access which has left some users with quite a few Excel files with a combination of sharepoint and SQL data connections which now have issues accessing the SQL servers locally.

I've had some success making use of the on premise gateway with Excel files with a data model I've tested, but I've been unable to find any documentation related to hitting data sources/files stored in SharePoint, is this even possible? And how would it handle authentication?

I guess putting the file in SharePoint would be the exact opposite issue, or can Excel files access stored in SharePoint make use of the on-Premis Gateway?

I've attempted to research this but I've been unable to find an awful lot of quality documentation, any help pointing me in the right direction would be much appreciated.

Thanks.

Matt 

[Anonymous · Accepted solution]

@Anonymous,

Do you combine sharepoint online file and SQL Server database as external data sources in Excel file? If so, you can use on-premises gateway in this case.

Firstly, in Excel file, make sure that you load data to data model when connecting to the two external data sources in Excel file.


Secondly, in Power BI Service, please enable the "allow user's..." option for your gateway as shown in the following screenshot.



Thirdly, you can set schedule refresh for the excel file containing external data sources in Power BI Service.


Regards,
Lydia

[Anonymous]

Thank you for such a detailed response Lydia, one question however, can you confirm if it matters how the data is added in Excel to the data model? Is it ok if the data has been added with Power Query and then added to the data model? I don't see any additional data locations appear in the Power BI gateway as in your screen shot above, just the one entry for the DB connection.

[Anonymous]

@Anonymous,

It is OK to use Power Query option in Excel, just make sure that you select the Add this data to the Data Model option in the Load To dialog box. For more details, please check "How do I make sure data is loaded to the Excel data model" part in this official article: https://docs.microsoft.com/en-us/power-bi/refresh-excel-file-onedrive.


Regards,
Lydia