Power Query Debuts for Excel for Mac but with Significant Gaps

April 30, 2023 - by Bill Jelen & Suat M. Ozgur

In early 2023, the Power Query tools debuted for Excel for Mac. This is a great first step, but many people will be disappointed that Power Query in Excel for Mac does not have options for:

• From Table or Range
• From Web
• From Folder
• From PDF
  

This article will provide easy workarounds to use From Table or Range, from Web, or From folder in Power Query for Mac.

From Table or Range

1. Make a note of the table name - perhaps it is tblFoo
2. Data, Get Data, Blank Query.

   

3. In the Power Query Editor, change Source = "" to Source = Excel.CurrentWorkbook(){[Name="tblFoo"]}[Content]
4. Click Next and Power Query Mac Excel shows a preview of the table. You can now do any other transformations as usual.

   

To see a demo, watch: https://youtu.be/chBlyDrejHo?t=77

From Web

We use exactly the same method that we used in the previous step - From Table or Range.

1. Data, Get Data, Blank Query.
2. In the Power Query Editor, change Source = "" to Json.Document(Web.Contents("https://demo.batcoder.com/api/?k=power%20bi"))
3. Click Next and Power Query shows a list of records returned from the web service in JSON format. You can now do any other transformations as usual.

   

Note: You can use XML.Document function to get data from XML sources instead of JSON.

To see a demo, watch: https://youtu.be/chBlyDrejHo?t=240

Get Data, From File, From Folder

This one is trickier. Let's say you want to combine all files from one folder with code like this:

Source = Folder.Files("/Users/bill/Desktop/MyData/")

When you click Next, Mac says that you are not allowed to Access the folder.

So, before you go to Power Query, do steps 1 to 6. You only have to do this once per folder per user.

1. From Mac Excel, launch the VBA Editor - fn + Option + F11
2. Control + Command + G to display the immediate window
3. Type the following and press Enter:

   Dir("/Users/bill/Desktop/MyData")

   

4. Mac pops up a warning that says "Additional Permissions are Required to Access the Following Files: /Users/bill/Desktop/MyData

   

5. In that dialog, click Select...
6. In the subsequent dialog, click Grant Access.

   Once you have done step 6, then Apple knows it is okay for Excel (and also Power Query) to access the folder.

7. Make sure to Quit and re-open Excel.
8. Data, Get Data, Blank Query
9. Change Source = Folder.Files("/Users/bill/Desktop/MyData/")
10. Click Next
11. The data preview shows a list of files. Click the Combine Files query in the header of the Contents

    

Any folder that will be created in this folder in the future will be also accessible from Power Query without additional permission requirements. However, if you rename the allowed folder then you will lose all access from Power Query (as well as VBA) and the folder should be allowed again.

To see a demo: https://youtu.be/chBlyDrejHo?t=317

Additional info: See Ron de Bruin's great article about Problems with Apple’s sandbox requirements that reveals the folders trusted as default for Excel and also all Office documents.

From PDF

Although we can use File.Contents function to import any file type, since PDF converter doesn't exist in Power Query on Mac, we don't have Pdf.Tables function to parse PDF documents. Therefore, at least for now, we can't get data from PDF files in Power Query on Mac.

***

Granted, neither of these is as easy as in Windows, but it definitely can be done if you have a notepad with a few lines of code to copy and paste.

MoreQuery Add-in for Mac (365 only)

Download the free add-in provides the missing Power Query options (and more) on Mac discussed in this article.

Download MoreQuery v2.1 for Mac »

Read this article for all add-in details.

Version 2.1 (6/18/2023): New Features:

• It is now possible to import files from the subfolders.

Version 2.0 (5/19/2023): New Features:

• The Import Data dialog

  

• Get Data From Existing Connection

  

• Create connection-only query

Version 1.0 (5/8/2023): Initial Version - Get data from:

• From Table/Range
• From Folder
• From Web Service

Title photo by Thomas Bormans on Unsplash