Get & Transform (Power Query) in Excel for Mac: Phase 1

For several years now, the Windows platform has introduced a powerful set of Get & Transform Data / Power Query tools, starting way back with Excel 2010. We have a Power Query blog each Wednesday that keeps you apprised of all the things you can do with the Extract, Transform and Load (ETL) tool. But it’s been missing from Mac Excel.

Back in June, there was a little teaser of what was to come with the ability to undertake limited Power Query query refreshes for CSV, JSON, Text, XLSX and XML files only. Well, today, Microsoft has announced the first step towards full support for Power Query in Excel for Mac.

[Given they have officially called it “Power Query in Excel for Mac” does this mean the Get & Transform moniker is to be retired..? We shall have to wait and see!]

This “Phase 1” release allows you to refresh your Power Query queries on Mac from Text, CSV, XLSX, XML and JSON files (yes, we’ve just said most of that has been around from June), but you can author queries with VBA.

This new functionality is live for all Office 365 subscribers in Excel for Mac running version 16.29 (19090802) and later. If you are running an earlier version, you will need to update.

To refresh queries generated from Text, CSV, XLSX, XML or JSON files, triggering the Refresh command is done the same way as previously, e.g.

• Click on the Data tab of the Ribbon, then choose ‘Refresh All’
• Right-click on your query table, then click Refresh
• Right click on your PivotTable, then click ‘Refresh Data’
• Use a VBA script
• etc.

It should be noted that the first time you try to refresh your workbook queries, you may need to update the location of the data source so that it works on your Mac. To do this, click the Data tab on the Ribbon -> Connections -> select the desired connection -> click Change File Path button to update it.

Furthermore, you may also create and manage Power Query queries in your workbook using VBA. Any existing macros and VBA scripts that reference Workbook.Queries and WorkbookQuery entities in the Excel's object model will work in Excel for Mac as expected. You may still need to adjust your scripts to reflect the notion of a file path on a Mac (e.g. /Users/USERNAME/Desktop/data.csv instead of C:\Users\USERNAME\Desktop\data.csv).

Remember, this is only the start (“Phase 1”): with Microsoft pushing to have “one Excel”, you can bet your bottom dollar further features will be coming soon. After all, Mac Excel is still a long way behind. Maybe they will Get the picture soon and Transform it shortly.