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 (29-April-2023) :

getdata2

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

getdata3

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

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

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

And here is a link to a 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 create an 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/

More information about the From Folder option


Note: the last version of the add-in fix the problem if you have subfolders, there is a bug now in PQ that it not have the correct path in the the Folder Path column for the sub folders.

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 i must enter into the Blank Query is to create a AppleScript file. Open the Script Editor on your Mac and choose New Document and paste the script below in it and Save it as the name you want (for example GetPath) in the File Format Application on your Desktop, see the File Format dropdown to choose Application. You can double click the GetPath.app file on your Desktop now to get the path of the active Finder window and other script lines on the clipboard so you can do a Command a and command v to replace the 4 lines in the blank Query.


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.


If you want to open the browse window in the Root folder where you can add your folders so you not have the problems with apple's Sandbox, you can replace the third line in the script with this one : 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.
23/03/2024
Web design by Will Woodgate