Combine Files no longer working

[anonymous_one]

Hi there,

I've been using the power query tool within a speadsheet to combine and consolidate 200 or so spreadsheets (which are saved down in a folder) into a single tab of data. I've been doing this successfully for a long time but recently it has stopped working . I can load the data but in the power query editor when I press the combine button (ie. with the 2 downward arrows), it briefly shows a "evaluating query" pop up message but then it disappears and nothing happens. It used to take me to a preview page, where I could choose which tab in the spreadsheets to combine, etc.

To give you a sense of the data, it's around 190 spreadsheets which I have saved in a single folder, each of them have various amounts of data but have the same headings. I have self-taught myself how to use power query but the way i've been using it is everytime I need to do this exercise with a fresh set of data (every quarter), I go into the same spreadsheet, go into edit power query and go into Source "edit settings" abd re-direct the source to the new folder where the new data sits. It then "loads". And then I press the combine button, and it usually works.

190 seems alot but I have no problems in the past up till now.  I have amassed over 100 of historic queries in this spreadsheet, which I was thinking might be the problem. I have deleted them all but the problem still persists.

I've tried going into task manager and changing the excel priority to "high" and closing all other programs down but that doesn't work either.

If i split the data into 2 chunks ie. combine 80 spreadsheets first, and then the next 80, it seems to work , but now and again. It's bit hit and miss. Has anyone had the same problem or know what's going wrong here?

Thank you in advance.
Ian

[anonymous_one · Accepted solution]

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.

[rubayatyasmin]

@anonymous_one 

Just a thought. 

Can it be a RAM and processing power issue? As you mentioned about High usage on Task manager. Can you try it on another device where RAM and processing power are more than your current device? 

or maybe try scripting outside PQ to combine the files then add to PQ. I am not certain that it is the option but just an idea. 

or maybe use PBI Dataflow if that's an option. 

[anonymous_one]

Hi, it might be a ram issue, don't know. It's not something i can solve though, I'm stuck with the machine I have.

[Anonymous]

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

[anonymous_one]

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.   

[anonymous_one]

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 😞

[El_Conquistador]

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.