Use external data in workbooks in SharePoint Online
Suppose that you want to share a workbook that brings in data that resides somewhere else, such as on a server. You want to be able to view and refresh the data in the workbook in a browser window, and you want other people to be able to do those things as well. You can easily create and share workbooks that people can view and refresh in a browser window. Just make sure the workbook contains a Data Model that uses data sources that are supported in SharePoint Online.
Use data sources that are supported in SharePoint Online
Some—but not all—of the data sources that you can use in Excel are supported in SharePoint Online. When a data source is supported, it means that you can typically refresh the data in the workbook in a browser window. You can use the following kinds of data sources in workbooks in SharePoint Online:
Microsoft Azure SQL Database tables
SharePoint lists that are available as OData feeds
Microsoft Azure Marketplace data
External data that is available through Business Connectivity Services with Secure Store Service (contact an administrator for help with this)
SQL Server data that is available in the Power BI Admin Center (this requires a subscription to Power BI for Office 365 and an administrator to configure the connection)
Work with a Data Model
In Excel, you can connect to an external data source and use that data connection to create charts, tables, and views. You can use external data in a Data Model—or not. But, if you want to share the workbook with other people in Office 365, make sure to add the data to the Data Model in Excel. That way, you’ll have an efficient workbook that contains a data source that you can use to create all kinds of views, including Power View sheets. For more information, see Create a Data Model in Excel.
Get more details
More detailed documentation is available on TechNet. See TechNet Article: Data sources supported for workbooks in SharePoint Online