Great news: Power Query debuted for the Mac in early 2023.
Bad news: It is missing four important connectors:
• From Table/Range - Fixed in this video
• From Web - Fixed in this video
• From Folder - Fixed in this video
• From PDF - no solution yet.
Thanks to Suat Ozgur - our resident Mac expert at MrExcel, we have solutions for three of those connectors.
Table of Contents
(0:00) Problem Statement
(1:17) From Table or Range
(2:44) How to edit query
(3:33) New icons in Applied Steps
(3:59) From Web
(5:17) From Folder
(9:45) No solution for From PDF yet
(10:27) Thanks to Excel Team
(11:15) Subfolders granted access
(11:24) Avoiding VBA
Bad news: It is missing four important connectors:
• From Table/Range - Fixed in this video
• From Web - Fixed in this video
• From Folder - Fixed in this video
• From PDF - no solution yet.
Thanks to Suat Ozgur - our resident Mac expert at MrExcel, we have solutions for three of those connectors.
Table of Contents
(0:00) Problem Statement
(1:17) From Table or Range
(2:44) How to edit query
(3:33) New icons in Applied Steps
(3:59) From Web
(5:17) From Folder
(9:45) No solution for From PDF yet
(10:27) Thanks to Excel Team
(11:15) Subfolders granted access
(11:24) Avoiding VBA
Transcript of the video:
[ MrExcel:} Oh this is a great video.
We are going to fix three things that are missing from Power Query in Macintosh.
Great news: 2023: Power Query debuts in Macintosh. But it does not support three things that we really need: From Table or Range.
From Web, from Folder, or From PDF.
And in this video – a long video, I admit.
We are going to fix From Table, From Web, and From Folder even.
Which requires a little bit, believe it or not, one line of VBA.
Thanks to Suat from MrExcel.
He joined me via Teams this morning.
Let’s take a look.
Today, I have with me Suat Ozgur, who is my resident Mac expert here at MrExcel.
He is joining us via Teams and he has figured out solutions to all three of those.
This is great.
I am going to talk to Suat.
You are going to see how to do all three of those, today in Power Query for the Mac.
Suat, how are you today?
[ Suat:} Hi Bill.
Thank you very much.
How are you?
[ MrExcel: ] I am doing great.
I can’t wait to see what you have to show us today.
[ Suat: ] Let’s start with From Table or Range.
[ MrExcel: ] Table or Range.
That's the one, every time I am in Power Query, I'm using from table or range because I'm just demoing something quick.
[ Suat: ] In Mac, there's no such such options.
There are so many options in Windows.
But on Mac we only stuck with these nine options.
For now, of course.
I believe that they are going to add all of them in time.
This is still great work, but we don't have the essential one.
[ MrExcel: ] Right.
[ Suat: ] Data from Range on Mac.
We have to do an an extra step here.
OK, we need to create a table first.
On Windows it does that automatically, but here we need to do that.
So I created my table here.
And I name it like something SampleTable.
And get this name.
And let's just go to Power Query and open the Power Query editor.
New Query.
Blank Query.
OK, all we need is.
Using a single line M code here.
And I'm just going to copy and paste it here.
We are using Excel Current Workbook and using the Sample Table.
[ Suat: ] Okay we just got this table and we can just Close and Load.
Then we just need to format dates or whatever.
But, as a simply the table data coming from the current range and then we can just work with this data.
[ Bill: ] Okay, and then let's see how do I get back into that query?
So normally, Data, Queries and Connections, Edit the Query.
How do you edit the query in Mac?
[ Suat: ] In Mac we still have Queries and Connections.
And, if I click on Queries and Connections, it doesn't open a sidebar here, but instead it just opened this.
But still I cannot go to, there's no edit.
OK, no.
But we can still go to Power Query editor.
And then our query is there.
So we can just edit our query here.
[ Bill: ] OK, so now that you have the table, let's do something with it.
Let's pivot, I don't know, just so choose category.
Pivot column.
[ Suat;} Yeah.
Yeah, makes sense.
And we have this and we can use everything from here just like in Windows.
But we have this little icons here.
[ Bill ] Oh yeah, From Table or Range.
And it's just one line of code.
I'm going to put that in a little notepad file.
Anytime I need to do From Table or Range, I'll just copy from the Notepad, Paste into the editor and change the table name to match.
And yeah, we're good to go.
[ Suat: ] Maybe not, I don't know.
Because when we see something doesn't exist here.
[ Bill: ] You just assume it doesn't work.
[ Suat: ] Yeah, right.
Yeah, it just doesn't work.
[ Bill: ] Let's do Web.
Let's do Web Next.
Because web is simple, right?
[ Suat: ] Yeah, very simple actually, Yes.
This is a web server that returns the JSON Data from MrExcel books.
And then I'm just going to use this API.
What we need to do is using the web contents command.
And converting its contents as a JSON to as a list, a table.
This is the actual line.
We are getting web contents by using this URL.
And then we are converting it to something meaningful.
Okay so we just need to use this command line.
And the next, and then we got the data just as we are going to do in Windows.
But still we have a To Table.
So we just convert this list to a table.
And then expand columns here.
And then yeah, we got the web data.
[ Bill: ] Nice.
And still it doesn't make sense why they don't have this.
[ Bill: ] Yeah, right.
Both of those are a single line of code, right?
It's not that hard.
[ Suat: ] Definitely.
[ Bill: ] The third topic today is how to do get all of the files from a folder, right?
Which is one of the beautiful things that's been in Windows Power Query since almost the very beginning.
[ Suat: ] This was really more difficult than the other two.
Mac doesn't let you to explore folders freely.
Again, I need to create my query myself.
Blank query.
OK this is the command line.
This is the command line that I can get the files from a folder.
Cuma, Desktop and it's named Data Folder, right?
Data folder?
I do this.
No, it doesn't let me.
But no, I can't access this.
[ Bill: ] And this is the Mac sandboxing system preventing you from doing that right?
[ Suat: ] Yes, and it also happens in VBA when I try to access a folder, it just blocks me.
But different from Power Query, it is just asking me to grant access to that folder to Excel.
So if we go to VBA on Mac.
[ Bill: ] Let's let's assume that a lot of viewers have never used VBA on the Mac.
And here we are in Excel, we have to do this one line of code in VBA one time.
So the three keys that you press are what?
[ Suat: ] It's the function key.
It's the left button key on your keyboard on your Mac keyboard.
And then Pption key next to it.
And then F11.
[ Bill: ] Perfect.
[ Suat:} You can just go to View and then click here this command button.
Or you can also use the shortcut Command Option G here.
Let's use this command to access.
Users.
Cuma is my cat's name.
And desktop and was named Data folder.
Data folder.
When I do this, VBA forces user to grant folder access here.
So I press Select.
My folder and then Grant Access.
[ Bill: ] If we just switch back to Power Query right now, is it going to work?
[ Suat: ] No, it's not.
You have to quit.
I'm not talking about closing this file.
You have to actually quit Excel.
And then you have to start Excel.
Now you can go to Power Query.
Create a blank query.
Once again you have to use this command line.
Which is the simple command for getting the content of a folder.
And then desktop and then Data folder.
[ Bill: ] Now there we are.
[ Suat: ] And then this is really good.
[ Bill: ] That great icon there in that first, to combine everything.
You still get to choose, just like you do in Windows.
[ Suat: ] Yes, it's going to create a sample pattern for you to combine the files.
[ Bill: ] In this video now we've had three lines of M code, one for table, one for web.
One for from file and one line of VBA code.
So, 4 lines of code would have made Power Query on the Mac, you know, twice as good.
We all, we all.
Everyone watching appreciates the fact that you went through five days of hell to figure this out and make it down to four lines of code for us.
[ Suat: ] Yes, I just wanted to make this easy for everybody who is using Mac.
And because I've been seeing these questions online all the time.
Especially even yesterday someone was asking about “Can I access files in a folder on Mac?
Why not?” So now it's it's it's just easy.
This one line call in VBA.
[ Bill: ] Alright, that's great.
So making Power Query on the Mac even more powerful by giving us access to From Table or Range, From Web and from Files from Folder.
And just because I know someone's going to ask down in the YouTube comments, The other big one that's missing is From PDF.
But we have no solution to that, right?
[ Suat: ] No, PDF, there's a PDF connector on Windows.
But it doesn't exist on Mac.
I don't know why, but it's it's just not a library yet.
Power Query can still get the file content as a file into its container.
But there is no connector, there is no adapter to read that file.
Convert that file from PDF to to get the tables in it and put it into Power Query.
So it just doesn't work yet.
[ Bill: ] Alright, good.
[ Suat: ] But I believe that I certainly believe and congratulate the Excel team.
Even for this part it's working great.
Really, I mostly need a Power Query using both in Windows on Windows and on Mac.
Before probably 3 months ago I didn't have this much.
I was just able to use everything by using M code and limited capability.
But now?
It's it's really catching Windows.
[ Bill: ] That's good.
[ Suat: ] Thank you very much, Bill.
Thank you for letting me explain all this to Mac users.
[ Bill: ] All right, thanks to everyone for watching.
We'll see you next time for another net cast from MrExcel.
Hey, two more important notes from Suat.
If you grant access to a folder using that line of VBA, that same permission grants access to all subfolders of that folder.
And then for anyone who wants to do this without VBA, there is a way.
But it's painful.
And once you see all of this that follows in the outtake.
You're just going to admit that VBA, one line of VBA, is the way to go.
But let's take a look at the other way.
[ Suat: ] Behind the scenes, actually.
I would like to explain this because this is important.
You don't need to do that.
You can go to your users Library folder.
And Containers.
And then Excel Data folder.
And Library.
Finally, Preferences folder.
There is a file here which is a Plist file.
That Apple uses for settings.
In the content you are going to see all these files are bookmarked here.
This is the folder name if you'd like to access that folder.
[ Bill: ] But you can't just edit it with whatever your equivalent of notepad is because it's a binary file.
If you like these videos, please, down below.
Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
We are going to fix three things that are missing from Power Query in Macintosh.
Great news: 2023: Power Query debuts in Macintosh. But it does not support three things that we really need: From Table or Range.
From Web, from Folder, or From PDF.
And in this video – a long video, I admit.
We are going to fix From Table, From Web, and From Folder even.
Which requires a little bit, believe it or not, one line of VBA.
Thanks to Suat from MrExcel.
He joined me via Teams this morning.
Let’s take a look.
Today, I have with me Suat Ozgur, who is my resident Mac expert here at MrExcel.
He is joining us via Teams and he has figured out solutions to all three of those.
This is great.
I am going to talk to Suat.
You are going to see how to do all three of those, today in Power Query for the Mac.
Suat, how are you today?
[ Suat:} Hi Bill.
Thank you very much.
How are you?
[ MrExcel: ] I am doing great.
I can’t wait to see what you have to show us today.
[ Suat: ] Let’s start with From Table or Range.
[ MrExcel: ] Table or Range.
That's the one, every time I am in Power Query, I'm using from table or range because I'm just demoing something quick.
[ Suat: ] In Mac, there's no such such options.
There are so many options in Windows.
But on Mac we only stuck with these nine options.
For now, of course.
I believe that they are going to add all of them in time.
This is still great work, but we don't have the essential one.
[ MrExcel: ] Right.
[ Suat: ] Data from Range on Mac.
We have to do an an extra step here.
OK, we need to create a table first.
On Windows it does that automatically, but here we need to do that.
So I created my table here.
And I name it like something SampleTable.
And get this name.
And let's just go to Power Query and open the Power Query editor.
New Query.
Blank Query.
OK, all we need is.
Using a single line M code here.
And I'm just going to copy and paste it here.
We are using Excel Current Workbook and using the Sample Table.
[ Suat: ] Okay we just got this table and we can just Close and Load.
Then we just need to format dates or whatever.
But, as a simply the table data coming from the current range and then we can just work with this data.
[ Bill: ] Okay, and then let's see how do I get back into that query?
So normally, Data, Queries and Connections, Edit the Query.
How do you edit the query in Mac?
[ Suat: ] In Mac we still have Queries and Connections.
And, if I click on Queries and Connections, it doesn't open a sidebar here, but instead it just opened this.
But still I cannot go to, there's no edit.
OK, no.
But we can still go to Power Query editor.
And then our query is there.
So we can just edit our query here.
[ Bill: ] OK, so now that you have the table, let's do something with it.
Let's pivot, I don't know, just so choose category.
Pivot column.
[ Suat;} Yeah.
Yeah, makes sense.
And we have this and we can use everything from here just like in Windows.
But we have this little icons here.
[ Bill ] Oh yeah, From Table or Range.
And it's just one line of code.
I'm going to put that in a little notepad file.
Anytime I need to do From Table or Range, I'll just copy from the Notepad, Paste into the editor and change the table name to match.
And yeah, we're good to go.
[ Suat: ] Maybe not, I don't know.
Because when we see something doesn't exist here.
[ Bill: ] You just assume it doesn't work.
[ Suat: ] Yeah, right.
Yeah, it just doesn't work.
[ Bill: ] Let's do Web.
Let's do Web Next.
Because web is simple, right?
[ Suat: ] Yeah, very simple actually, Yes.
This is a web server that returns the JSON Data from MrExcel books.
And then I'm just going to use this API.
What we need to do is using the web contents command.
And converting its contents as a JSON to as a list, a table.
This is the actual line.
We are getting web contents by using this URL.
And then we are converting it to something meaningful.
Okay so we just need to use this command line.
And the next, and then we got the data just as we are going to do in Windows.
But still we have a To Table.
So we just convert this list to a table.
And then expand columns here.
And then yeah, we got the web data.
[ Bill: ] Nice.
And still it doesn't make sense why they don't have this.
[ Bill: ] Yeah, right.
Both of those are a single line of code, right?
It's not that hard.
[ Suat: ] Definitely.
[ Bill: ] The third topic today is how to do get all of the files from a folder, right?
Which is one of the beautiful things that's been in Windows Power Query since almost the very beginning.
[ Suat: ] This was really more difficult than the other two.
Mac doesn't let you to explore folders freely.
Again, I need to create my query myself.
Blank query.
OK this is the command line.
This is the command line that I can get the files from a folder.
Cuma, Desktop and it's named Data Folder, right?
Data folder?
I do this.
No, it doesn't let me.
But no, I can't access this.
[ Bill: ] And this is the Mac sandboxing system preventing you from doing that right?
[ Suat: ] Yes, and it also happens in VBA when I try to access a folder, it just blocks me.
But different from Power Query, it is just asking me to grant access to that folder to Excel.
So if we go to VBA on Mac.
[ Bill: ] Let's let's assume that a lot of viewers have never used VBA on the Mac.
And here we are in Excel, we have to do this one line of code in VBA one time.
So the three keys that you press are what?
[ Suat: ] It's the function key.
It's the left button key on your keyboard on your Mac keyboard.
And then Pption key next to it.
And then F11.
[ Bill: ] Perfect.
[ Suat:} You can just go to View and then click here this command button.
Or you can also use the shortcut Command Option G here.
Let's use this command to access.
Users.
Cuma is my cat's name.
And desktop and was named Data folder.
Data folder.
When I do this, VBA forces user to grant folder access here.
So I press Select.
My folder and then Grant Access.
[ Bill: ] If we just switch back to Power Query right now, is it going to work?
[ Suat: ] No, it's not.
You have to quit.
I'm not talking about closing this file.
You have to actually quit Excel.
And then you have to start Excel.
Now you can go to Power Query.
Create a blank query.
Once again you have to use this command line.
Which is the simple command for getting the content of a folder.
And then desktop and then Data folder.
[ Bill: ] Now there we are.
[ Suat: ] And then this is really good.
[ Bill: ] That great icon there in that first, to combine everything.
You still get to choose, just like you do in Windows.
[ Suat: ] Yes, it's going to create a sample pattern for you to combine the files.
[ Bill: ] In this video now we've had three lines of M code, one for table, one for web.
One for from file and one line of VBA code.
So, 4 lines of code would have made Power Query on the Mac, you know, twice as good.
We all, we all.
Everyone watching appreciates the fact that you went through five days of hell to figure this out and make it down to four lines of code for us.
[ Suat: ] Yes, I just wanted to make this easy for everybody who is using Mac.
And because I've been seeing these questions online all the time.
Especially even yesterday someone was asking about “Can I access files in a folder on Mac?
Why not?” So now it's it's it's just easy.
This one line call in VBA.
[ Bill: ] Alright, that's great.
So making Power Query on the Mac even more powerful by giving us access to From Table or Range, From Web and from Files from Folder.
And just because I know someone's going to ask down in the YouTube comments, The other big one that's missing is From PDF.
But we have no solution to that, right?
[ Suat: ] No, PDF, there's a PDF connector on Windows.
But it doesn't exist on Mac.
I don't know why, but it's it's just not a library yet.
Power Query can still get the file content as a file into its container.
But there is no connector, there is no adapter to read that file.
Convert that file from PDF to to get the tables in it and put it into Power Query.
So it just doesn't work yet.
[ Bill: ] Alright, good.
[ Suat: ] But I believe that I certainly believe and congratulate the Excel team.
Even for this part it's working great.
Really, I mostly need a Power Query using both in Windows on Windows and on Mac.
Before probably 3 months ago I didn't have this much.
I was just able to use everything by using M code and limited capability.
But now?
It's it's really catching Windows.
[ Bill: ] That's good.
[ Suat: ] Thank you very much, Bill.
Thank you for letting me explain all this to Mac users.
[ Bill: ] All right, thanks to everyone for watching.
We'll see you next time for another net cast from MrExcel.
Hey, two more important notes from Suat.
If you grant access to a folder using that line of VBA, that same permission grants access to all subfolders of that folder.
And then for anyone who wants to do this without VBA, there is a way.
But it's painful.
And once you see all of this that follows in the outtake.
You're just going to admit that VBA, one line of VBA, is the way to go.
But let's take a look at the other way.
[ Suat: ] Behind the scenes, actually.
I would like to explain this because this is important.
You don't need to do that.
You can go to your users Library folder.
And Containers.
And then Excel Data folder.
And Library.
Finally, Preferences folder.
There is a file here which is a Plist file.
That Apple uses for settings.
In the content you are going to see all these files are bookmarked here.
This is the folder name if you'd like to access that folder.
[ Bill: ] But you can't just edit it with whatever your equivalent of notepad is because it's a binary file.
If you like these videos, please, down below.
Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
