Learn how to use Power Query with Excel Online. While Excel Online is quickly catching up to the feature set of the Excel Desktop App, there are still a number of limitations that require a different way of thinking about cloud based spreadsheets and your existing processing workflow.
Excel Online continues to add features to bring it closer to parity with Excel for Desktop. It’s an incredibly capable spreadsheet tool on its own and is extremely affordable and easy to get started with when compared to some of the more expensive Microsoft 365 Tiers. There are currently some limitations to using Power Query with it, but there are also some workarounds worth being aware of.
Let’s jump in!
Table of Contents
What is Power Query?
Power Query is a data transformation and data prep tool that started in Excel 2010 as a subset of features called Power Pivot. Since then, it’s evolved to become part of Power BI for business intelligence and Microsoft Fabric for data integration and engineering. It provides a quick way to record and replay data transformation steps and can connect to hundreds of data sources.
The following screenshot is from the Excel Desktop App to highlight the feature set to show the interface for those who are less familiar with it.
Data transformations are applied at the column level, and are tracked on the right side under Applied Steps. When you press Close & Load, the data is loaded into a data model or a separate table in your workbook.
Is Power Query Available in Excel Online?
Excel Online offers limited support for Power Query at this time. In October 2022, Microsoft announced the availability to Refresh Power Query using Excel Online, but does not offer the capability to create, edit or delete Power Queries using Excel Online at this time.
The following screenshot shows a Power Query that was created using Excel for Desktop and saved to OneDrive. Once the .xlsx file is in cloud storage, it can be opened in Excel Online where you can refresh a pre-existing Power Query.
When working with this method, there can be a number of limitations depending on where your data source is located. For example, on premises data will largely be inaccessible because the cloud service won’t have the same security setup as your work desktop or laptop.
Workaround Using Power BI Datasets with Excel Online
Excel Online has the capability to connect to Power BI Datasets, which has a number of advantages. Data can be imported or connected to from Power BI then transformed using the same Power Query interface as Excel.
Once imported, data can be published to the Power BI Service at PowerBI.com and set to automatically refresh on a set schedule or directly connect to live data sources or SQL databases.
Power BI is a cloud first solution that receives a lot of attention and development from Microsoft with new features being added on a monthly basis. However, it’s worth noting that the full Power Query editor for Power BI is only available using the Power BI Desktop app at this time, with limited features available using Power BI’s online editor.
It’s likely that once Power Query Online for Power BI is more mature, it will be re-packaged and deployed with Excel Online.
When will the Full Power Query Editor Come to Excel Online?
In the announcement, Microsoft mentions that they are working on integrating all of the features of Power Query into the Excel Online experience but did not provide a timeframe of when it’s expected to rollout. Checking the Microsoft 365 Release Roadmap we can see that there is some recent development allowing Excel Online to refresh queries from Authenticated data sources, but no word on full Power Query capabilities.
Another resource to keep an eye on is the official Microsoft Excel Blog that highlights new and upcoming features across all versions of Excel.
It’s impossible for us to make any type of accurate guess as to when Power Pivot will become available in Excel. We get the feeling that while Excel Online does receive regular updates, it does not receive the number of resources that other Microsoft programs have been receiving, like Power BI.
While Excel Online has added tons of features over the last several years and is becoming closer to the feature set offered in Excel for Desktop there are still a number of limitations. The biggest limitations are the lack of the ability to create and edit Power Query and VBA for Macros are not supported, though in the case of macros you can re-create much of the functionality with Excel Type Script.
Microsoft has not announced when a full Power Query editor will be available for Excel Online, but there are indications that it is in the roadmap. Power BI datasets can be used as an alternative with a limited Power Query Online editor available through the Power BI platform which is more heavily invested in and aggressively updated.