Microsoft Excel is a very popular spreadsheet tool used for a variety of purposes in both personal and enterprise use, including data crunching, reporting, and analysis. Microsoft regularly adds new features to the software, including easier edit access and a revamped UX to fetch data from external sources. Now, the company has made some further improvements in the latter area.
Excel for the web has recently netted a very important feature: the ability to refresh authenticated data sources leveraging Power Query. Previously, if you wanted to refresh an external data source in Excel for the web, you would need to download the file locally and apply the refresh there before syncing it to the web version or uploading it there.
With the latest enhancement, you can simply apply Refresh All through the Data tab or refresh individual data sources through the Queries pane. Currently, refreshes are limited to 1,000 connections per user, and once you trigger a refresh, you may be prompted to enter your credentials to the authenticated data source, which includes SharePoint, SQL Server, Web API, and more. You can continue working on other aspects of your workbook while the refresh completes.
In the same vein, it is now possible to view and manage data source credentials for queries authored by Power Query through the Data > Data Source Settings tab. Users have the ability to view, delete, and modify credentials at a global level or just for their current workbook. They will also be able to authenticate to a data source that already exists in the Excel workbook through the same tab.
Both these capabilities are now available for all Excel for the web customers. Microsoft says that its future plans include the migration of the full-fledged Power Query experience from desktop to the web, but it is yet to share a public timeline.