Microsoft is finally checking off one of the biggest items on the Excel power user wishlist. The full Power Query experience is officially rolling out to the web app, which means you can import, transform, and automate your data workflows without ever opening the desktop app.
Power Query, Excel’s ETL (extract, transform, load) powerhouse, lets you connect to disparate data sources, clean up messy information, and reshape it into something actually useful. We’re talking about fixing column headers, removing nulls, and reshaping data with just a few clicks. It’s the kind of automation that saves hours of manual copy-pasting misery.
Related
5 everyday actions that Power Query does better than regular Excel tools
Replace manual Excel tasks with conditional columns, smart merging, the unpivot tool, and more.
While Excel for the web has improved by leaps and bounds over the last few years, it was essentially the diet version of the app. If you received a file with a complex Power Query setup, you often couldn’t even refresh it on your browser if it required specific authentication. Yes, you could open a workbook and look at the cleaned data, but you couldn’t access the engine room to change the underlying query logic or pull in fresh data from a new source. You’re probably familiar with the dreaded “feature not supported” notification that forced you to go back to the heavy desktop app just to perform what should have been a simple task.
However, now, you can use the online Get Data wizard to import data from sources like SQL databases, SharePoint lists, OData feeds, and other workbooks directly in your browser. What’s more, Excel for the web can handle cloud-based handshakes (OAuth2) directly, meaning you can refresh data from a SharePoint list or an SQL server without ever leaving your browser tab. Once the data is in, you can use the many tools to automate your data transformations. By bringing the full editor to the browser, Microsoft is effectively removing one of the last major hurdles to a purely cloud-based Excel workflow.
Related
4 Excel Power Query Commands You Need to Know
Become an expert data manipulator.
Getting started is straightforward and strikingly familiar if you’ve used Power Query Editor in the desktop app:
- Open a workbook in Excel for the web.
- Navigate to the “Data” tab on the ribbon.
- Click “Get Data” to choose your source (such as Text/CSV, Excel Workbook, or SQL server).
- Once connected, click “Transform Data” to launch the Power Query Editor.
- After making your changes, click “Close & Load” to return the cleaned data to your sheet.
Credit: Microsoft
This is a massive win for collaboration. Instead of one person owning the data cleaning on their local machine, any team member with the right permissions can hop into the browser and tweak the query logic. Similarly, you no longer have to worry if a teammate is running an older version of Excel on their PC that doesn’t support a specific Power Query function—as long as they have a browser, the query will work exactly the same way for everyone. It’s also a huge gain for flexibility—whether you’re working on a Chromebook or a borrowed laptop at a coffee shop, you now have the same data-crunching capabilities you’d expect on a high-end workstation.
One important caveat: for now, your data sources generally need to be live in the cloud. While the desktop app can easily grab a CSV from your My Documents folder, the web version thrives on sources like OneDrive, SharePoint, or web APIs. Microsoft has confirmed that local file support is in the roadmap, but we aren’t quite there yet.
Viewing and refreshing existing queries is now available to all Microsoft 365 subscribers. However, the ability to create and edit queries is currently restricted to Microsoft 365 subscribers on Business or Enterprise plans. In terms of the future plans for Power Query in Excel for the web, Microsoft says it aims to “add data sources and advanced features,” such as more connectors and the ability to pull from local files. While there’s no official word on when (or if) the full editor will roll out to personal or family Microsoft 365 accounts, it’s a giant leap forward for professional data crunchers.
Source: Microsoft Excel Blog
OS
Windows, macOS, iPhone, iPad, Android
Free trial
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
