Forum Discussion
Combine Files no longer working
UPDATE: Thanks all for your helpful replies on this, and your most recent post, El_Conquistador
It was solved in the end: I realised if I had one of the spreadsheets I wanted to combine actually open when I had the Power Query window open, the combine function would fail as per above. But once I closed the spreadsheet, it worked!
Only discovered this fortuitously . I often had one of the spreadsheets I was trying to combine open so I could see better the columns I was working with. Hope that helps others with this same issue.
But if you have all of the files in the same folder, and if you are using the Get Data From Folder option, why do you need to change the the folder path every month? Are you writing these to a tab and then loading them into Power Query?
--Nate
Hi Nate, I actually create a new folder each time I do it (every quarter) and dump the new quarter's spreadsheets into there, so I re-direct the query to a new folder each time. I use the same consolidation file each quarter (well, it's just a 'saved as' file from the last quarter and re-naming it) which is where I access the power query and load the results into. But I don't use the "get data from folder" option each time. What I do is, once I've "saved as" the consolidation file ready for this quarter's data, I go into the power query editor by right clicking on the power query data table loaded from last time, and then change the source of the power query to redirect it to the new quarter's folder (which contains the new set of 200 or so spreadsheets.)
To do this, I change the folder file path by right clicking on the "Source" in the Applied Steps window on the right hand side of the screen:
(I have to clear all the other Applied Steps first eg. Filtered Hidden Files etc as the steps might be slightly different this time around)
This will then bring up the 200 spreadsheets listed as 200 rows in the editor screen. At this point i'll click combine data, and thats when it fails.
I have tried getting the data from scratch ie. using the Get Data from Folder option in the menu bar at the top, but that also fails when I try to load and transform 😞
I know this is an old thread however, I know what the issue is. If the folder your trying to get data from has an invalid character, especially # a hash tag at the front of the folder name, this issue will occur. Hopefully this will save others time and hassle when this problem occurs.