I wanted to express my gratitude to you for creating this incredible tool that has made such a significant difference in the MacOS excel. The functionality and usability of the tool you developed are truly exceptional. Thanks again.
Thanks for the feedback! Glad to hear it helps. Hopefully the Power Query Development Team will provide a native way doing this, so there will be more happy Mac Excel users on the world :)
Amazing! Thank you so much. Re. the 'From Folder' section: I'm using Excel for Mac Version 16 and the Print Dir("/Users/username/Desktop/foldername") VBA code didn't work for me (it generated the compile error: "method not valid without suitable object"). I used MsgBox Dir("/Users/username/Desktop/foldername") instead and that worked fine.
Glad to hear it helps, @mujuw. Note about the error. You should be using Print in a module instead of using it in the Immediate window as it is shown in the video. If you use Print in a module then VBA takes it as the Print statement which is to write data into a file. However, the Print method used in the immediate window is a method of Debug object. You need to use it as Debug.Print in a module (but as Print in the immediate window). That's why you are seeing the error, because VBA thinks that you are trying to call Print statement and write something into a file.
This worked!! Incredible, thank you. I just spent 3 hours looking for solutions. Note for anyone who got stuck or hit an error - the code is "Folder.Files". The cursor in the video hid the full stop in between the two words, which took me a couple of tries to figure out/pause the video at the precise moment. Thanks again
A quick note - I found that I had to make sure the 'tables' that were in each of my each excel files, were actually inserted as tables. I opened each file, inserted my table as an actual table, then saved the file. Then in Power Query I had to select the option of Table1 as opposed to the file name when combining files otherwise it didn't work.
Hi, Andrew. Thanks for your comment. Although I personally wouldn't do that and give Full Access to Excel for this on Mac (considering VBA is very powerful to destroy an unprotected system, like Windows), this was my first day struggling with OSx permissions. I gave up on it at the end of the day (right, it doesn't work). Believe me, com.microsoft.Excel.securebookmarks.plist file is the key, access must be granted and that folder should be registered/bookmarked in that file. We should either edit the file directly and duplicate a key by changing the UUID and path for the requested path or use the VBA trick. I prefer VBA because editing the binary file requires converting it to XML first, then editing, and then back to the binary form. I don't even mention browsing for the .plist file in the file system. The VBA method looks still much easier to me.
@@suatmozgur Well, you should also be able to use System Settings>Privacy & Security>Files and Folders (which I think is the GUI for editing the plist file), but that is notoriously difficult and so probably your VBA hack is simplest :)
@Andrew Miskin Perhaps it works for Excel 2016 & 2019 for Mac but it certainly doesn't work in Office 365 - at least not for me, neither full access nor files & folders. I can understand that, because, for example, MacScript was blocked in 2016, however, it works in 365!
Hi! Thank you for the amazing help! With regards to the import from folder for Mac, I'm trying to to close&load to a pivot table but do not have the option and just clicking close&load seems to be unable to load. Any way around this?
I am using Version 16.86 (24060916) with license purchased already, but on Macos don't have power query get data from folder, on Window they do have, how can I find this fixed, although using the method in video but still the same please share if you guys have the same issue :(((
Hi When I tried the VBA it didn't pop up anything for me to grant access. Can you recommend why is that? In fact it opened a dialogue asking me chose a VBA name etc.
I am getting the access denied error for some, not all CSV files in a directory. I modified the M code after the initial query was built to make use of a filepath parameter that I change when I want to load from different CSV files, all of which have the same format, but data for different date ranges. The macOS permissions are the same and it looks like after Excel loads them it adds the com.apple.quarantine attribute to them. I am at a loss as to what to do and would greatly appreciate any assistance. I tried running the one line of VBA code for the folder containing the CSV files, even though some were already accessible, but that did not help,with the files I am getting the access denied error for.
Did anyone else have an issue with hidden files appearing when trying to add files from a folder? Update - I copy and pasted the files to a new folder and did the process again and it worked. It may also be that I hadn't selected the folder itself when granting access, and that's why I had the issue.
You lost me at the very first step of "Copy + Paste". Copy what? How? I typed in the formula you had, exactly as written (except for my table name) and it returned an error. Everyone here seems to love the video, but I can't even sort out the first step.
Instead of typing the code during the video, I just copied it from notepad or somewhere else to save some time. If you typed the exact code line that is pasted in the video and it didn't work, then you don't have the correct table name. Otherwise, please let us know the error. The code line is: Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]
This. Thank you for this - it is amazing! I am stuck on the very last part, though. When combining my Excel files, I get a "[DataFormat.Error] File contains corrupted data." error message for every file. There are no passwords, and the folder/files are saved on my desktop. The files open properly when opening in Excel as usual. All of the headers are all the same and each files is formatted the same. Do you have any thoughts on this? Cheers!
Hey there ! I'm a new mac users and I have a problem with combining and repeat two table with power Query. For examples I have 2 set of table : (1) Outlet/Store name (2) Discounted item/SKU names . I need to create a new table where for each store, all the discounted item/SKU in table (2) is listed. When I use excell in windows, I can just create a connection with one of the table ( i.e table (1)) > open the query > close and load > only create connection. Then i use add coloumn function on table (2) to to this. But I cant find a way to do this on Mac, because there is no option to close and load table only as connection in mac. Can you help with this problem ?
Hello MrExcel! Your video provided excellent information. I have a related question: Is it possible to establish a Power Query connection to a web service such as OneDrive for Business or to a shared library on a SharePoint Online site in Office 365? Currently, I have an Excel file with multiple Power Query connections, all stored in OneDrive and synchronized to my Mac. However, when another user with permissions syncs the same directory and opens the Excel file, they cannot update it because the Power Query connections are local to my machine (OneDrive). Thank you for your assistance. Note: This text has been translated from Spanish to English using ChatGPT 3.5.
Hello, this troubleshooting method didn't help me. Can you suggest why? I've written a VBA macro that dynamically determines the path to the folder containing the file from which data needs to be obtained. This is done to avoid rewriting the file path every time it's moved to a different location. After that, I pull this path into my Power Query query and concatenate the full path with the file name from which data needs to be retrieved. However, the problem persists, and it still says 'no access to the file'. P.S. I've just discovered that this action needs to be done not with the folder, but with the specific files that require access. For some reason, access to all folders and files is not granted, and only when I specified the full path to the file along with the filename, access to that file was granted. When I simply specify the folder where the files are located to grant access, nothing works.
From Folders: it works till I try to combine. It gives me error for 2 of the 3 files included in the folder. But the 3 files I created as test are basically the same. Any suggestions? Thanks!
I am trying to follow and run the Dir("/ command but mine does not run the command. For anyone who may be facing the same issue I found the solution, if you are saving in a cloud then add the folder to local it will work.
Hi, thank you for the video. This was a very helpful workaround for accessing folders in Excel's power query on a Mac. It worked great and I was able to combine many csv files in a query before loading them onto my spreadsheet. However, I saved the Excel workbook and when I came back to it later, the Power Query editor says that my access to the original folder with all the csv files is denied. I tried running the same code in VBA again but that did not work. Do you know what else may be causing this problem/ how to fix it?
Hi, after quitting excel and starting a new blank query, I am getting the following error: [Expression.Error] The import Folders.Files matches no exports. Did you miss a module reference? Details isRecoverable: True isExpected: True Help!? Appreciate your work.
@bradleystring4076 - I just noticed the error points to a wrong function. Please take a look at the query and change Folders.Files to be Folder.Files. There is no Folders function in M language. In fact, another note, the most recent version of the MoreQuery add-in is not using Folder.Files but Folder.Contents. I am not sure which version you are using, but please take a look at the query and make the change I suggested. Also, I recommend using the most recent version of the add-in.
Are you trying to Grant Access to a folder with special permissions, such as a OneDrive folder? I am not entirely sure, but something else should be preventing access to that folder. Did you try it by using another path?
I'll also attached your video in Udemy's comment section. currently taking up Udemy's Power Query course. Let me know if it's ok to put your video there.
I do not understand how the "from web" worked. at all. where did that formula come from? and i have tried to use that with other URL's and i have had no luck, can someone please help
Web.Contents() and Json.Document() are M functions also existing on Mac. You'll get the same formulas when you do this on a Windows machine in the background. However, the Excel Developer Team didn't implement a quick way of doing this, especially for JSON sources, because it works without any problem on Mac as well. Are you using a remote JSON document as a source? If you can access the URL through a browser then these functions should be also able to do the same. However, it must be a valid JSON document. I wonder about the error you are receiving, if any.
See 4:00 in the video please. You need to use Web.Contents and Json.Document functions together in order to read JSON from an API. For more information, make a search for MoreQuery for Mac. Note: Retrieving data from an API might be more complicated when the authorization is required, or pagination exists. This video and MoreQuery add-in don't cover these topics.
I use the Excel.CurrentWorkbook command all the time in Windows, so I'd definitely have worked out the first one! It's a really useful command if you just want to return a single cell, simply name the cell and then use the aforementioned code in PQ. It saves the unnecessary overhead of turning absolutely everything into a table. Because I learned PQ before the intellisense adaption, most of these commands are in my memory. I'm wondering if the prompt for folder permissions can be got round by adding a connection to the folder first? Much the same as you would when adding a connection to a SQL database? Replacing the connection string with a folder path, much like you would do in VBA to connect to another excel file for editing without opening via adodb.
@Rico S It is impressive to learn M without IntelliSense! That's the only reason I use Windows for Power Query for practicing M code. Right - no IntelliSense on Mac! But I will still keep my Mac on my desktop :) Any file/folder attempt through VBA requires access permission if the file/folder is not already in the green area (in the security bookmark .plist file in other words), so I don't think we could avoid the prompt. (Spoiler alert - I am currently working on something to make this much more user-friendly.)
I get an erorr! ---------- Message ---------- [Expression.Error] The import FolderFiles matches no exports. Did you miss a module reference? ---------- Session ID ---------- f6ef94a1-20d3-42e8-a1f8-1c0aedfa3388 ---------- Mashup script ---------- section Section1; shared Query = let Source = FolderFiles("/Users/billymitchell/Downloads/For Billy") in Source;
It worked! Although there is an issue with ds_store extensions, these are hidden and shouldn’t be deleted due to Finder’s requirement for it. It is possible to delete with terminal commands but not recommended. Is there a way to exclude these ds_store extensions from the query using the editor?
