Forum Discussion
Merge multiple excel files into different tabs of one workbook using Power Query on Mac
Here is the variant, but made on Windows
Just for Mac users who would like to use Power Query, a legend came across this post and told me that there is indeed a work around to use "Get Files From Folder" on Mac.
Here is the youtube video:
https://www.youtube.com/watch?v=chBlyDrejHo&t=318s
Turns out we just need to grant Excel access to the folder we want to use as source on Mac. Just one line of VBA code can do the trick.
After granting Excel access to "Documents/dev.nosync/MyTest/DataFolder" using VBA, below code would work:
With the help of TTodorov , I finally figured out the correct buttons to press in power query UI and find a painless way to get this done. (Many thanks again for offering a long-term solution for this nuisance!)
Mainly, first follow below to hack the source folder on mac
https://www.youtube.com/watch?v=chBlyDrejHo&t=318s
Then follow below to select the columns I need based on an external config:
https://www.youtube.com/watch?v=-owZ7G880Jc
Come up with something like the attached.
Still struggling to figure out how to write ForEach loop. (and I suspect my Mac is partially to blame here). so I will just live with pressing buttons for now.
Here is the variant, but made on Windows
- It works on Mac! I just have to add a step to filter by file extension because on Mac we have this ".ds_store" file that cannot be filtered out by "#"Filtered Hidden Files1" = Table.SelectRows(GetContent, each [Attributes]?[Hidden]? <> true),".
The code is so well structured that I finally start to feel brave enough to open the Advanced editor to try to write something.
Thanks a lot!
After examining each line of code you sent me more carefully, I finally figured out why it doesn't work on my Mac.
The reason is quite stupid, and it is exactly what you suspected initially: that is, the external table "ColumnsToLoad" doesn't work in a for loop on Mac. (or maybe, on a Mac, loading an external table and using it as a global variable is just different from Windows, I have no ideas...).
I had to hard code a table in Advanced Editor to get your code to work:
ConfigTable = #table(type table [ColumnsToLoad = text, DataType = text], {{"ETF Ticker", "text"}, {"Name", "text"},{"Ticker", "text"}, {"Identifier", "text"}, {"SEDOL", "text"}, {"Weight", "number"}, {"Sector", "text"}, {"Shares Held", "number"}, {"Local Currency", "text"}}),I attached the spreadsheet to demonstrate the problem.
In the Query "Transform Sample File", Table.NestedJoin works just fine:
But if I copy over the code to the custom function "TransfromFile", and then invoke that "TransformFile" in my main query, I get below error:
I had to write the custom function "TransfromFile" like below. that is, I just cannot use the table "ColumnsToLoad" that sits in Excel Sheet1.
Hopefully there is a way around this. because hard-coding excel table in M code is just pathetic!
