Power Query in Mac Excel


On this page you will find some information about the Power Query option in the latest updates of Mac Excel. You will find this menu option on the Data tab in the Get & Transform Data group named : Get Data (Power Query)

getdata1

When you click on it you will see this (11-July-2024) :

getdata2

When you click on Get Data (Power Query) button you will get the options below :

getdata3

If you look at the options that you have on your Mac you miss a few important options, you find them in Excel for Windows but not have them on the Mac on this moment.

  1. From Table or Range
  2. From folder
  3. From Web (JSON/XML)
  4. From PDF
  5. From Existing Connection

The good news is that it is possible to do and it is not so very difficult. Bill Jelen posted very good info that he created together with Suat Ozgur that show you how, for the Folder option I have add some useful information in the section below the links.

Here is a link to the page on mrexcel about it :
https://www.mrexcel.com/excel-tips/power-query-debuts-for-excel-for-mac-but-with-significant-gaps/

Tip: And Suat Ozgur also make a great add-in, that you can find on this page and also all the info you need: https://www.mrexcel.com/board/excel-articles/morequery-for-mac.68/

My own tips about the From Folder option


Suat show you how to avoid the problem with the 'you are not allowed to Access the folder' warning with one VBA code line. You can avoid this extra step if you use a folder in another location on your Mac like I show you on my website on this page : https://macexcel.com/examples/setupinfo/sandbox/

The easiest way to create the folder and link to it on your Desktop is to use this script file one time so it is easy to access the folder :
https://macexcel.com/examples/setupinfo/setup/

I know that the folder path link is longer but I see that not as a problem because I never type them myself, because I use this trick like I show you below if I want the folder path :

First show the Path Bar in Finder, you must do this one time :

  1. Open Finder
  2. Click on View in the Finder Toolbar
  3. Click on Show Path Bar

Open the folder with the files in Finder and right click on the folder name in the Path Bar and choose
Copy "MacOfficeFiles" As Pathname

When you paste it you will get this for example :
/Users/rondebruin/Library/Group Containers/UBF8T346G9.Office/MacOfficeFiles The only thing you must do is to add the / at the end of the path in Power Query. So the Source line looks like : Source = Folder.Files("/Users/rondebruin/Library/Group Containers/UBF8T346G9.Office/MacOfficeFiles/")

Tip: Another way that I like to get the information that i must enter into the Blank Query is to create a AppleScript file that create the complete M code for me. Open the Script Editor on your Mac and open a new file and paste the script below in it and Save it with the name you want (for example GetPath) on your Desktop. Use the File Format Application, see the File Format dropdown to choose Application. You can double click the GetPath.app file on your Desktop now to get the M code lines with the path of the active Finder folder and copy it on the clipboard so you can do a Command a and command v to replace the 4 lines in the Blank query. To open a Blank Query go to the Data tab in Excel and in the Get & Transform Data group click the button named : Get Data (Power Query) and choose Blank query.

The example script below will create the M code that takes the file path of the active folder in Finder and filter on xlsx files and filter on Date modified. Note: Use Folder.Contents if you not want to get the files in the subfolders.


Tip : I like to add my script.app files in the Script Editor menu in the top menu bar on your Mac. Open the Script Editor and use Command , to open Settings and mark Show Script menu in menu bar and also mark Show Computer scripts.
scripteditorsettings
If you have done it correct you see this menu item now on top of your Mac, to add script files to this menu choose 'Open Scripts Folder > Open Users Scripts Folder' to open the folder where you can copy the GetPath.app file in. This way you can always access your app files because you can always access the menu on top of your Mac.

Scripteditormenu


Tip: You can also open a app file like this with VBA : Shell ("/Users/rondebruin/Library/Scripts/GetPath.app")

Tip
: If you want to Browse to the folder you can create another .app file with the script below.


Tip: It now open the browse window in the Root folder where you not have problems with apple's Sandbox, you can replace the third line in the script if you want to open in another folder you want : set therootPath to (path to the home folder) & "Library:Group Containers:UBF8T346G9.Office:" as string

More information about Power Query

Tip: if you want to learn Power Query from the masters buy this book : Master Your Data with Excel and Power BI. This book is created for Win Excel but you can learn a lot of it when you start with Power Query on the Mac. Click here to go to the skillwave site where you can buy the digital book.

MasterYourData_BookCover


Note : Check out this page the coming months because I will add some examples and check out the differences between Power Query in Windows and on the Mac.
05/09/2024
Web design by Will Woodgate