Power Query for Mac Excel is missing From Table/Range.
Power Query for Mac Excel is missing From Folder.
Power Query for Mac Excel is missing From XML.
Power Query for Mac Excel is missing From JSON.
A new More Data add-in from Suat Ozgur at MrExcel adds all four of these functionalities to Excel for Mac! Download the add-in from this article: MoreQuery for Mac
The FREE, open-source add-in adds From Table/Range to Power Query for Mac. It adds From Folder. It adds From XML. It adds from JSON.
Table of Contents:
(0:00) More Data Add-In fixes Power Query for Mac
(0:37) Get Data From Table/Range on Mac Power Query
(1:18) Get Data From Folder on Mac Power Query
(2:43) Get Data from JSON or XML on Mac Power Query
(3:27) Wrap-up
Power Query for Mac Excel is missing From Folder.
Power Query for Mac Excel is missing From XML.
Power Query for Mac Excel is missing From JSON.
A new More Data add-in from Suat Ozgur at MrExcel adds all four of these functionalities to Excel for Mac! Download the add-in from this article: MoreQuery for Mac
The FREE, open-source add-in adds From Table/Range to Power Query for Mac. It adds From Folder. It adds From XML. It adds from JSON.
Table of Contents:
(0:00) More Data Add-In fixes Power Query for Mac
(0:37) Get Data From Table/Range on Mac Power Query
(1:18) Get Data From Folder on Mac Power Query
(2:43) Get Data from JSON or XML on Mac Power Query
(3:27) Wrap-up
Transcript of the video:
[ Bill: ] This is amazing. My friend Suat, he now has a free add-in for Mac Excel users.
To bring these items back to Power Query.
From table or range, from folder, from JSON or XML. Let's take a look.
Suat, welcome back. [ Suat Ozgur: ] Hi, Bill.
Thank you for having me back. Yeah, we have good news. We can use this add-in on Mac to create our Power Query queries and easily instead of using all those stuff that we explained in the previous video.
So that's [ Bill: ] So this is great. Their Power Query is called "Get Data".
Your add-in is called "Get More Data". I love that! [ Suat Ozgur: ] Yes.
It is more query from table or range. It's just like working on Windows.
And then if it is not already a table, it creates table for us and opens the Power Query editor.
Just like here, all you needed to transfer or do something that you would like to do...
We're probably adding some custom column here.
And then do anything else that you would like to do. [ Bill: ] We get all the tools of Power Query.
What this is doing is it's building in that connection string that's connecting to the table that you just created. [ Suat Ozgur: ] Yes.
Just like on Windows.
[ Bill: ] All right, so one of the three is all table or range. Let's see the good one, from folder.
How do we do that?
[ Suat Ozgur: ] It is also easier here, so I just copy and paste folder path here and then click OK. Showing me this grant file access dialogue.
Click select, and then on the next screen, click grant access, and then you got it. It's here.
So just select Excel files and then combine them.
Then that's it. Okay.
[ Bill: ] Even creates all the Transform files, just like Windows does.
[ Suat Ozgur: ] Yes. [ Bill: ] Beautiful.
Okay. Load that.
Load that and show us the one problem that Mac has.
[ Suat Ozgur: ] Yes.
If I load this, it is going to create my table beautifully.
However, you will see these additional worksheets, [ Bill: ] And that's because...
[ Suat Ozgur: ] That's because...
[ Bill: ] Power Query for Mac doesn't have the concept of connection only.
Those are all connection only in Windows, but we don't have that in Mac. So they just show up.
But you can delete them from the grid, right? [ Suat Ozgur: ] Yes.
But you can just simply delete those. [ Bill: ] When you delete those extra things.
It still is refreshable. [ Suat Ozgur: ] Of course!
All we need to do is right click and refresh. [ Bill: ] Perfect.
All right, that's beautiful. Okay.
Now the next one we're missing is from web.
On Windows they let us put in HTML and we can choose which tables we want, and that's not possible here in Power Query for Mac, and it's not possible within your add-in, but you do cover two other connectors, and that's...
[ Suat Ozgur: ] JSON and XML connectors and API.
For example, I'm going to use here is the API that returns [ inaudible 00:03:10 ] so it's just creating this little code for us and then this list that contains the data coming from the API, coming from the website, and I can expand all this information here just like before.
Close and load, it just close and loads beautifully here.
So we can just transform this data however we want. [ Bill: ] Beautiful.
Now there's a much longer article that we're going to link to, and it covers a lot of reasons why Suat did things one way or the other.
A lot of technical details there, but I wanted this to be a quick video because most people are just going to go download more_data.xlam, install it, and wow, their power query for Mac is going to have a lot of the things that were missing from the first edition!
[ Suat Ozgur: ] Absolutely. Yes.
I hope this will help many Mac users. [ Bill: ] That's perfect, Suat.
And you're making this available for free, right?
[ Suat Ozgur: ] Yes, this is going to be for free and it is also open source.
You can see the all entire code and see how things are working and you can even improve it, create from PDF version.
I would love to see that. [ Bill: ] That would be great.
Yeah. Yeah.
[ Suat Ozgur: ] It is still missing. [ Bill: ] Right.
Good. Let's hope that someone does that.
All right, Suat, this is great.
The YouTube description has the link to the article where you can download this add-in for free.
I want to thank you for spending the last week making this easier for all those Mac folks.
[ Suat Ozgur: ] I would like to thank you for encouraging me to do this because you really pushed me to do this. I mean, yeah.
Thank you, Bill. [ Bill: ] That's great.
[ Suat Ozgur: ] That was great. [ Bill: ] Good.
Okay, great, and thanks everyone for stopping by.
We'll see you next time for another net cast from Mr. Excel.
To bring these items back to Power Query.
From table or range, from folder, from JSON or XML. Let's take a look.
Suat, welcome back. [ Suat Ozgur: ] Hi, Bill.
Thank you for having me back. Yeah, we have good news. We can use this add-in on Mac to create our Power Query queries and easily instead of using all those stuff that we explained in the previous video.
So that's [ Bill: ] So this is great. Their Power Query is called "Get Data".
Your add-in is called "Get More Data". I love that! [ Suat Ozgur: ] Yes.
It is more query from table or range. It's just like working on Windows.
And then if it is not already a table, it creates table for us and opens the Power Query editor.
Just like here, all you needed to transfer or do something that you would like to do...
We're probably adding some custom column here.
And then do anything else that you would like to do. [ Bill: ] We get all the tools of Power Query.
What this is doing is it's building in that connection string that's connecting to the table that you just created. [ Suat Ozgur: ] Yes.
Just like on Windows.
[ Bill: ] All right, so one of the three is all table or range. Let's see the good one, from folder.
How do we do that?
[ Suat Ozgur: ] It is also easier here, so I just copy and paste folder path here and then click OK. Showing me this grant file access dialogue.
Click select, and then on the next screen, click grant access, and then you got it. It's here.
So just select Excel files and then combine them.
Then that's it. Okay.
[ Bill: ] Even creates all the Transform files, just like Windows does.
[ Suat Ozgur: ] Yes. [ Bill: ] Beautiful.
Okay. Load that.
Load that and show us the one problem that Mac has.
[ Suat Ozgur: ] Yes.
If I load this, it is going to create my table beautifully.
However, you will see these additional worksheets, [ Bill: ] And that's because...
[ Suat Ozgur: ] That's because...
[ Bill: ] Power Query for Mac doesn't have the concept of connection only.
Those are all connection only in Windows, but we don't have that in Mac. So they just show up.
But you can delete them from the grid, right? [ Suat Ozgur: ] Yes.
But you can just simply delete those. [ Bill: ] When you delete those extra things.
It still is refreshable. [ Suat Ozgur: ] Of course!
All we need to do is right click and refresh. [ Bill: ] Perfect.
All right, that's beautiful. Okay.
Now the next one we're missing is from web.
On Windows they let us put in HTML and we can choose which tables we want, and that's not possible here in Power Query for Mac, and it's not possible within your add-in, but you do cover two other connectors, and that's...
[ Suat Ozgur: ] JSON and XML connectors and API.
For example, I'm going to use here is the API that returns [ inaudible 00:03:10 ] so it's just creating this little code for us and then this list that contains the data coming from the API, coming from the website, and I can expand all this information here just like before.
Close and load, it just close and loads beautifully here.
So we can just transform this data however we want. [ Bill: ] Beautiful.
Now there's a much longer article that we're going to link to, and it covers a lot of reasons why Suat did things one way or the other.
A lot of technical details there, but I wanted this to be a quick video because most people are just going to go download more_data.xlam, install it, and wow, their power query for Mac is going to have a lot of the things that were missing from the first edition!
[ Suat Ozgur: ] Absolutely. Yes.
I hope this will help many Mac users. [ Bill: ] That's perfect, Suat.
And you're making this available for free, right?
[ Suat Ozgur: ] Yes, this is going to be for free and it is also open source.
You can see the all entire code and see how things are working and you can even improve it, create from PDF version.
I would love to see that. [ Bill: ] That would be great.
Yeah. Yeah.
[ Suat Ozgur: ] It is still missing. [ Bill: ] Right.
Good. Let's hope that someone does that.
All right, Suat, this is great.
The YouTube description has the link to the article where you can download this add-in for free.
I want to thank you for spending the last week making this easier for all those Mac folks.
[ Suat Ozgur: ] I would like to thank you for encouraging me to do this because you really pushed me to do this. I mean, yeah.
Thank you, Bill. [ Bill: ] That's great.
[ Suat Ozgur: ] That was great. [ Bill: ] Good.
Okay, great, and thanks everyone for stopping by.
We'll see you next time for another net cast from Mr. Excel.
