Power Query | Mac Excel Automation

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)

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

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

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.

From Table or Range
From folder
From Web (JSON/XML)
From PDF
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 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. But 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 :

Open Finder
Click on View in the Finder Toolbar
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 in the Power Query editor 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. So the Source line looks like : Source = Folder.Files("/Users/rondebruin/Library/Group Containers/UBF8T346G9.Office/MacOfficeFiles/")

Another way to avoid Apple's Sandbox problems
If Power Query have a problem with the folder path after you paste it in the M code and press Next, for example if you use a folder on your Desktop. You can double click on Source in the Power Query editor and use browse to select the same folder to get it working, this is an easier way for folders that have a problem with Apple's sandbox then the VBA example that Suat show you on his page in my opinion.

Note : You must use this trick also when you use a folder with files in your OnDrive folder, I try to place the OnDrive folder in a location on my Mac without sandbox problems, but this makes a alias to the OnDrive folder in that folder and the real files are still in a location with sandbox problems. There where two paths when I tested it, the one with the files : /Users/rondebruin/Library/CloudStorage/OneDrive-RondeBruinExcelAutomation and the alias path '/Users/rondebruin/Library/Group Containers/UBF8T346G9.Office/OneDrive - Ron de Bruin Excel Automation', so no luck with trying to place the OnDrive folder in another location.

Read the part below for a very easy way to get the path and M code, we hope that Microsoft will add a folder option to Power Query in the future like we have in Win Excel.

My favorite way to get the path and create the M code

Another way that I like to use 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.

--10-7-24
--Ron de Bruin
--Get files from active Finder folder and filter on extension, and filter on modified date
--https://macexcel.com/examples/filesandfolders/powerquery/
tell application "Finder"
	if exists window 1 then
		try
			set PQfolder to window 1
			set thePath to (POSIX path of (target of PQfolder as alias))
			
			set wholecode to "let" & return & "Source = Folder.Files(\"" & thePath & "\")," & return & "#\"Filtered rows\"=Table.SelectRows(Source, each ([Extension]=\".xlsx\"))," & return & "#\"Sorted rows\"=Table.Sort(#\"Filtered rows\",{{\"Date modified\", Order.Descending}})" & return & "in" & return & "#\"Sorted rows\""
			
			display dialog wholecode buttons {"Copy to Clipboard", "Close"} default button 1
			if the button returned of the result is "Copy to Clipboard" then
				set the clipboard to wholecode
			end if
		on error
			display dialog "There is an error, close Finder and reopen Finder and run the app file again. Note : you get this error also if you have the Recents folder open in Finder." buttons {"Close"} default button 1
		end try
	else
		display dialog "There is no Finder window open, unable to get the folder path" buttons {"Close"} default button 1
	end if
end tell

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.

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.

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.

--10-7-24
--Ron de Bruin
--Browse to folder and get the files from it and filter on extension, and filter on modified date
--https://macexcel.com/examples/filesandfolders/powerquery/
tell application "Finder"
	try
		set therootPath to (path to the home folder) & "Library:Group Containers:UBF8T346G9.Office:" as string
		set thePath to POSIX path of (choose folder with prompt "Select the folder" default location alias therootPath)
		
		set wholecode to "let" & return & "Source = Folder.Files(\"" & thePath & "\")," & return & "#\"Filtered rows\"=Table.SelectRows(Source, each ([Extension]=\".xlsx\"))," & return & "#\"Sorted rows\"=Table.Sort(#\"Filtered rows\",{{\"Date modified\", Order.Descending}})" & return & "in" & return & "#\"Sorted rows\""
		
		display dialog wholecode buttons {"Copy to Clipboard", "Close"} default button 1
		if the button returned of the result is "Copy to Clipboard" then
			set the clipboard to wholecode
		end if
	on error
		display dialog "Cancelled!" buttons {"Close"}
	end try
end tell

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.

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.