Power Query works across several Microsoft products, so whatever you learn for Power Query in Excel can be applied to Power BI and other products. Custom connectors. Easily extend Power Query by creating your own connectors and data transformations if. I've never needed to do that so can't really report how well it works for running Power BI, but it's worth a try. You can still use the features of PowerBI.com from your Mac (without running Windows), but that won't allow you full access to the report creation tools available in Power BI Desktop.
4 October 2019
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.
Powerpivot Mac Excel
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:UsersUSERNAMEDesktopdata.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.
In Excel 2016, Power Query isn’t an add-in — it’s a native feature of Excel, just like charts and pivot tables are native features. If you’re working with Excel 2016, you don’t have to install any additional components. You’ll find Power Query in Excel 2016 hidden on the Data tab, in the Get & Transform group.
If you’re working with Excel 2010 or Excel 2013, you need to explicitly download and install the Power Query add-in. As of this writing, the Power Query add-in is available to you only if you have one of these editions of Office or Excel:
- Office 2010 Professional Plus: Available for purchase through any retailer
- Office 2013 Professional Plus: Available through volume licensing only
- Office 365 Pro Plus: Available with an ongoing subscription to Office365.com
- Excel 2013 Stand-alone Edition: Available for purchase through any retailer
If you have any of these editions, you can install and activate the Power Query add-in. Simply enter the search term Excel Power Query add-in into your favorite search engine to find the free installation package.
Note that Microsoft offers Power Query for both Excel 2010 and Excel 2013 in both 32- and 64-bit platforms. Be sure to download the version that matches your version of Excel as well as the platform on which your PC is running.
Power Query Alternative For Mac
After the add-in is installed, activate it by following these steps:
- Open Excel and look for the Power Query command on the Insert tab. If you see it, the Power Query add-in is already activated. You can skip the remaining steps.
- From the Excel Ribbon, choose File→Options.
- Choose the Add-Ins option on the left, and then look for the Manage drop-down list at the bottom of the dialog box. Select COM Add-Ins and then click Go.
- Look for Power Query for Excel in the list of available COM add-ins. Select the check box next to each one of these options and click OK.
- Close and restart Excel.
Download Power Query For Mac
A successful install results in a new Power Query tab on the Excel Ribbon.