Uploading multiple

[jungle125]

Hi

I would like to know if there is a way to upload multiple Excel source workbooks/files into power query editor at a time. Currently I am only able add them one file at a time by using New Source -> File -> Excel Workbook. Problem is I have to upload 500 files, potentially more in the future.

Thanks in advance for your help.

 

[jdellasala]

Use get data from folder (Data -> Get Data -> From File -> From Folder). With 500 or more files, that may take a VERY LONG TIME! You should think about the approach before you start and end up in the mud.

• If there's some way to pull in fewer files into a single workbook such as by Year or Quarter in the file name, break it up that way by folder if possible such as a folder for each year. once you've done it once, you can make a copy of the query and just change the source folder.
• Use Transform - do NOT Combine. Do that later with a new Merge or Append
• When the first step comes up (Source), you'll be presented with a list of the files in the folder. Transform the Extension folder to Lower (or upper) case, and then filter for Excel files (equals .xlsx or equals .xlsx or .xlsm depending on your needs.
• As noted above, filter by some part of the Name (filename with extension) to narrow down the one query such as Contains "2022" to filter for only this year.
• Load as Connection Only and Add to the Data Model. Unless these are tiny files, you can't load move than 1,048,576 rows into a worksheet, and that's a VERY inefficient way to store the data and take up a LOT of disk space. Use Pivot Tables to analyze the data.

Hope that helps.

 

[jungle125]

Dear jdellasala

Thank you for your swift reply. I only started using power query a few days ago, so I´m still figuring things out. Your bullet points 1, 2 and 5 I understood. With points 3 & 4 I´m not so sure about the mechanics of it, but maybe if I give more context of what I´m trying to do you may suggest something different.

I am doing a historical statistical analysis on the stock market, starting off with the S&P500. I´ve downloaded all 500 csv files with historical open, close, high low, volume data. Each csv file contains between 2000 and 16000 lines, depending on how far back the market data goes. I then copy this data over to an excel template for which calculates and extracts 8 values which I´m analysing. At this point I still have 500 excl files of around 4MB each, but this I don´t mind because I also like to take a closer look at each stock once I´ve identified the interesting ones. I then want to tabulate these 8 values from each file into a "Master" spreasheet where I essentially have a table of 500 rows (one for each stock in S&P500) and 8 columns. As I´m not very proficient with power query yet, a workaround I´ve found to not have to manipulate any rows or columns, is to automatically send the 8 data points to a second sheet within each of the 500 excel workbooks. I then just pull out the 1 row with 8 columns and append it to the master spreasheet, which seems to work fine. The only problem is that it´s time consuming and after the S&P I want to tackle something like the Russel 2000, and my wrist is already starting to hurt...

Some remarks on your comments:
-> I could combine more csv files into one workbook, but I would run out of lines any way, and keeping it separate seams "cleaner" to me at this stage, even if more efficient.
-> I don´t have to filter to look for files, as I want to upload all 500 files in the folder in one go, but I just want those 8 data points from each file.

Any suggestions?

 

[jdellasala]

Dear jdellasala

Thank you for your swift reply. I only started using power query a few days ago, so I´m still figuring things out. Your bullet points 1, 2 and 5 I understood. With points 3 & 4 I´m not so sure about the mechanics of it, but maybe if I give more context of what I´m trying to do you may suggest something different.

I am doing a historical statistical analysis on the stock market, starting off with the S&P500. I´ve downloaded all 500 csv files with historical open, close, high low, volume data. Each csv file contains between 2000 and 16000 lines, depending on how far back the market data goes. I then copy this data over to an excel template for which calculates and extracts 8 values which I´m analysing. At this point I still have 500 excl files of around 4MB each, but this I don´t mind because I also like to take a closer look at each stock once I´ve identified the interesting ones. I then want to tabulate these 8 values from each file into a "Master" spreasheet where I essentially have a table of 500 rows (one for each stock in S&P500) and 8 columns. As I´m not very proficient with power query yet, a workaround I´ve found to not have to manipulate any rows or columns, is to automatically send the 8 data points to a second sheet within each of the 500 excel workbooks. I then just pull out the 1 row with 8 columns and append it to the master spreasheet, which seems to work fine. The only problem is that it´s time consuming and after the S&P I want to tackle something like the Russel 2000, and my wrist is already starting to hurt...

Some remarks on your comments:
-> I could combine more csv files into one workbook, but I would run out of lines any way, and keeping it separate seams "cleaner" to me at this stage, even if more efficient.
-> I don´t have to filter to look for files, as I want to upload all 500 files in the folder in one go, but I just want those 8 data points from each file.

Any suggestions?

Guess I should have used numbers instead of bullet points!
After step 2, you'll be presented with what looks like a worksheet with panels on the left and right, and for the data pulled from the folder specified columns like the attached screenshot.
RIGHT CLICK on the Extension column, select Transform -> lowercase.. The right panel of the screenshot shows every step you take, and this step is there.

Filter that column as you would an Excel table, as well as other columns you want to filter like the Name column to narrow down the number of files you pull in.

I totally understand how lost you feel, however believe me and several Excel Youtubers who swear that learning even the basics of Power Query has the potential of saving literally hours of work. I wish I had learned it when i was spending hours to reconcile 5 different inventory accounts a day. Now I could do the same in an hour at most.
MrExcel has an entire playlist on the subject with 51 short videos - Power Query.

There's also the Full Advanced Data Analysis & BI Class (MSPTDA) playlist from Mike at the ExcelIsFun channel. Highly recommended - Mike is amazing with PQ like Bill is with VBA, and his videos nearly always have downloadable worksheets with or for starting and finishing the lesson.

Going through all the steps you'd need here would be very counterproductive. If you run into a speed bump, give the forum a yell!