Excel for Mac: Power Query features and limitations

This week in our series about Microsoft Excel for Mac, it’s all about Power Query. For a long time, it didn’t even exist on Mac, but since it’s such a key feature of Excel, the team at Microsoft has been steadily introducing more and more Power Query capability to Excel for Mac. At the time of writing, it’s still missing a few features, but it at least has a good foundation.

Step 1 of the journey - Refresh

Which came first, the query or the refresh?  You can’t refresh unless there’s a query, right?  That’s correct, but in Excel for Mac, the ability to refresh came first.  This meant the query had to be created in Excel for Windows, and then you could refresh it on Mac.

If you’ve been using Excel for Mac for a long time, you might be aware that it didn’t have any Power Query features until mid-2019.  At that time, Microsoft added the ability refresh queries that were connected to text files. It seems like a very small step forward, but this was a big milestone, because it meant that the foundation of Power Query was there and working.

In 2020 you were able to refresh from more types of data sources, including tables / Tables within the workbook, SQL Server databases, and the first cloud data import capability, with support for files on SharePoint, SharePoint Lists, SharePoint Folder and OData.  While it didn’t enable much more capability, it showed a big foundational step for Power Query.  The ability to sign into online sources is important so that other data sources can be supported in the future.

Step 2 - 'Get data'

The next big step came in 2021, when the ability to “Get Data” was introduced.  You could now create new queries from local files to begin importing data from Excel files and text files.  Previously, you could refresh queries that were created on Windows, but there was no way to start a new query.  The only way to edit a query was by using a VBA workaround, which was not convenient.

Step 3 – Transform

In 2022, Microsoft announced that you could not only create new queries, but you could edit them as well with the Power Query editor on Mac.With this update, you could truly 'Get and Transform' your data.This was a major improvement that really brought Excel for Mac to a level where you can get some serious work done.

Prior to this improvement, if you picked a data source for a new query, there was no ability to do the Transform steps that makes Power Query so powerful.  Now, the full query editor is available on Mac, which is an amazing tool, so you can transform your data, even if you don’t have access to a Windows computer.

Remaining Limitations

Although creating and editing queries makes Power Query on Mac very capable, there are still a few limitations (at the time of writing) that you should be aware of.

• Some data sources are not supported. If you look at the 'Get Data' menu in Excel for Windows, you’ll notice a long list of data sources, many of which are not available on Mac.  One of the key sources is 'From Table/Range', but we’ll give you an easy workaround to this in a subsequent blog post.  The full list of data sources supported by different versions of Excel is published by Microsoft here - Power Query data sources in Excel versions.
• You can’t control the destination of the data from your queries, because the 'Load to…' dialog isn’t available on Mac.  This means that all queries will get loaded into Tables that appear in your worksheets.
• Even if the 'Load to…' dialog were available, you wouldn’t be able to add your queries to the Data Model, since Power Pivot also isn’t available on Mac.

Since the Power Query features have been gradually introduced to Excel for Mac, we expect more features to be added over time, so keep watching for updates on the Excel team’s blog - Excel Blog (microsoft.com).

We hope you find this topic helpful.  Check back for more details about Excel for Mac and how it’s different to Excel for Windows.

Word to the Wise

For your reference, we’ve included links to Microsoft’s Announcements about Power Query on Mac:

• May 2019 - A Journey to Power Query in Excel for Mac (microsoft365.com)
• July 2020 - Updates to Power Query in Excel for Mac (microsoft365.com)
• October 2020 - Journey to Power Query in Excel for Mac continues (microsoft365.com)
• May 2021 - Import data from local files with Power Query in Excel for Mac (microsoft365.com)
• May 2022 - Shape data with Power Query Editor in Excel for Mac - Microsoft Community Hub