We’re excited to share the latest addition to Excel for the web: Power Query Authenticated Refresh and Data Source Settings for selected data sources!
We’ve reached yet another milestone in Excel for the web: Power Query Refresh is now generally available for queries sourcing data from selected authenticated data sources.
As we released the ability to refresh Power Query data from anonymous data sources (link), it was only a matter of time until we added the ability to refresh Power Query data from authenticated data sources, which are the majority of data sources used, and require users to enter credentials.
This milestone also enables us to release Import with Copilot to Excel for the Web (following Win32 and Mac), as it relies on Power Query for refreshing data.
Getting started
These new functionalities are available to all users on Excel for the Web.
See this support article for more information on Power Query data sources in Excel versions.
GIF showing how to refresh a data source in Excel for the web using Power Query
Refreshing Power Query queries
You can now refresh the Power Query queries in your workbook that source data from a selection of authenticated data sources:
- Select the Data tab > then choose Refresh All
- Open the Queries Pane > then select Refresh
When you refresh a query, if authentication is needed, you can select the relevant method – anonymous, user and password, or your organizational account. For example, to refresh organizational data, select the respective method:
Your user will be automatically identified (you can also switch it, if needed), so you can easily click “Connect” to continue the refresh process.
The list of supported connectors includes:
- SharePoint* files (Excel workbooks, TXT, CSV, XML, JSON, PDF)
- SharePoint* folders
- SharePoint Online List
- SharePoint List
- SQL Server Database
- OData Feed
- Web API
- IBM Db2 Database
- PostgreSQL Database
- Azure SQL Database
- Azure Synapse Analytics
- Azure HDInsight (HDFS)
- Azure Blob
- Azure Table
- Azure Data Lake Storage Gen 1
- Azure Data Lake Storage Gen 2
- Azure Data Explorer
- Dataflows
- Dataverse
- Microsoft Exchange Online
- Dynamics 365 (Online)
- Salesforce Objects
- Salesforce Reports
*SharePoint/OneDrive for work or school
Note: Refresh is limited to 1000 connections per user.
The refresh happens behind the scenes so you can keep editing the workbook while refreshing.
Managing queries using Data Source Settings
You can now view and manage data source credentials for the Power Query queries in your workbook using Data Source Settings:
- Select the Data tab > then choose 'Data Source Settings’.
- Choose between ‘Current Workbook’ and ‘Global Permissions’ to view and manage data sources credentials in the current workbook or across all workbooks, respectively.
- To delete the credentials stored for a data source, click on the ‘Delete’ button.
- To edit the credentials stored for a data source, click on the ‘Edit credentials’ button.
In addition, we’re introducing a new functionality in Data Source Settings – authenticating to a data source that exists in the workbook from within the dialog:
- Select the Data tab > then choose ‘Data Source Settings’.
- Navigate to ‘Current Workbook’.
- Click on the ‘Add credentials’ button:
What’s next?
Future plans include releasing the full Power Query Editor experience to Excel for the Web.
Feedback
We hope you like this new addition to Excel and we’d love to hear what you think about it!
Let us know by using the Feedback button in the top right corner in Excel - add #PowerQuery in your feedback so that we can find it easily.
Want to know more about Excel for the web? See What's new in Excel for the web and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on Twitter.
Jonathan Kahati, Gal Zivoni
~ Excel Team
Microsoft
