Power Query has been available for Excel since 2010. Here is how to add Power Query according to your Excel version.: Good news, you do not need to download Power Query for Excel 2019, 2016 or in Office 365. You can find it in the “Data” ribbon under the button “Get & Transform”. The Microsoft Power Query functions in the table that follows are helpful text functions to know. You can use them to clean and manipulate textual strings. Function What It Does and How to Use It Text.Contains Returns true if a specified value is found within a given text field. Use this function with the If. The 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, find out how to update your Office for Mac. Refreshing Power Query queries. You can now refresh the Power Query queries in your workbook that pull data from Text, CSV, XLSX, XML or JSON.
Power Query documentation. Power Query is the data connectivity and data preparation technology that enables end users to seamlessly import and reshape data from within a wide range of Microsoft products, including Excel, Power BI, Analysis Services, Common Data Service, and more.
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.
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).
Power Query For Excel Mac
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.