Excel Update: Power Query Editor for Mac

Bottom Line: Learn about the new Power Query Editor that Microsoft just released for the Mac version of Excel.

Skill Level: Beginner

Watch the Tutorial

The Power Query editor for Mac is finally here!

Let’s take a look at how Power Query for Mac works, what some of the current limitations are, and if this means you should go by a Mac computer.

The Editor for the Mac version of Excel looks quite similar to the Windows version.

If you're not familiar with Power Query, it's a tool that allows you to bring in data from all different types of sources and then clean up that data. It’s a fantastic resource for automating data preparation, and it can save you lots of time. You can watch my overview of Power Query and get a better understanding of how it works here:

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool – Excel Campus

At the moment, the Power Query Editor for Mac is only available to the Insider's Beta Channel for Microsoft 365. Learn more about how to join for free here: https://insider.office.com/en-us/join/Mac.

It should be rolling out to other channels in the coming months.

Using the Power Query Editor in Mac

Let’s start by creating a query.  On the Data tab of the Ribbon, you'll notice a new button called Get Data (Power Query). It’s a split button that allows you to see these different options. Click on the option that says Get Data (Power Query).

That's going to open up a window where you can choose your data source. Currently, there are only two options.

As the Editor for Mac continues to develop, they will be adding more data sources.

Get Data From Text/CSV Files

We'll first look at getting data from a CSV file.

First, choose Text or CSV file. That's going to prompt us to browse for a file to pull into the Editor.

Once our data is visible in the Editor, we can do all types of transformations. For example, if we want to split a combined name column into first and last names, we can. Just right-click on the column, and then choose Split Column.

We want to split by the left-most delimiter, with the Space character as our delimiter. When we hit OK, that will run our transformation.

That will give us two columns now for our purchasers’ first names and last names. Of course, we can rename those columns. We can also delete columns or otherwise clean up our data as we like. Once those transformations are done, we just click the Close and Load button. This creates a new sheet in our workbook with our cleaned-up data.

Get Data from Excel Table

We can also pull Excel Tables into the Power Query Editor. Instead of selecting a text or CSV file, we would select the Excel Worksheet where the table resides and then we would see a list of the tables available to choose from.

At this point, we're not actually able to pull in a table from the same workbook that we're working on, at least not with the one-step process we’re used to seeing in the Windows Editor. Instead, we have to browse for the file and table that we want.

After selecting the sheet or table, the Power Query Editor opens where we can make transformations and load the results to the current workbook.

See the video above for an example.

Current Limitations

Microsoft is working on enhancing the Power Query Editor for the Mac version of Excel, so I expect that these limitations I mention will eventually get ironed out, hopefully in the near future.

No Connection Only Queries

The first limitation is the Close and Load button. As of now, it's not a split button and we don't have the option to create a connection only, as we do on Windows.

Let's say I wanted to combine three CSV files for Divisions 1, 2, and 3. If I had these queries on the Windows version, I could create a connection-only query, which means they wouldn’t create an output table in Excel when I close and load.  

With the Power Query editor for Mac, however, we will have to create and output those tables and then combine them. Fortunately, you still can do an append query to combine all of them.

The data will be stacked on top of each other for all three data sources.

No From Folder Option

Another limitation is there's no option to get data from a folder. With windows, we can add more CSV files into a specified folder and automatically combine those using Power Query. With Mac, we don’t yet have that capability.

Limited Data Sources

The other big limitation right now is just the limited number of data sources.  In Microsoft's announcement post they also mention SharePoint as a data source. I'm not seeing that option yet, but hopefully, it will come soon.

Again, all of these limitations should hopefully be temporary as the PQ Editor for Mac continues to be enhanced.

Should I buy a Mac now?

Eric Bremiller on LinkedIn asked if this new update puts the Windows and Mac versions of Excel on equal footing.

The answer is:

It depends…

Even with the addition of Power Query, there are still other limitations for the Mac.

1. Power Pivot is not currently available on the Mac version of Excel.
2. Power BI Desktop is also not available for the Mac OS.
3. The VB Editor is much more robust in the Windows version. The Mac version does not support the editing of userforms and has some other limitations.

So to answer Eric’s question directly, no, they are not yet on equal footing. It will really depend on if you need or use the features listed above.

One workaround for those who need to use a Mac is to implement virtualization software like Parallels or VMware Fusion for the Mac. With these, you can actually run Windows on a Mac computer, and therefore use the Windows version of Excel even though you are on a Mac.

There can be limitations with the virtualization options depending on the Mac you are using, so it's best to do your research on those workaround solutions as well.

Conclusion

I hope this quick overview of Power Query for Mac is helpful for you. I’d love to know what you think about the new feature, so leave a comment below with your first impressions.