cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MBaldy Frequent Visitor
Frequent Visitor

Excel data source functionality in Power BI

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Excel data source functionality in Power BI

@MBaldy,

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.
1.PNG

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


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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: Excel data source functionality in Power BI

@MBaldy,

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.
1.PNG

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


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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
MBaldy Frequent Visitor
Frequent Visitor

Re: Excel data source functionality in Power BI

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.

Moderator v-yuezhe-msft
Moderator

Re: Excel data source functionality in Power BI

@MBaldy,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.