Enhancing the Performace of Query for huge excel data

[Chandna]

Hi,
We do have 1000 excel files in a folder and each excel contains one spreadsheet with 15000 rows of data. We were reading the data from these 1000 files into a single excel sheet using POWER QUERY. For this we got a memory out of bound exception due to limited number of rows in a single excel sheet.
Our motive was,
STEP1: To read the data from all files and apply text filter on the entire data together.
STEP2: To find the count of the filtered rows (maximum will be around 2000).
There are additional functionality is to be performed, but this is currently out of scope of this query. Since, we are stuck at the initial step itself.
Alternative way,
We thought of another way wherein we thought of the following steps:
STEP1: To apply text filter on the data of each excel file individually.
STEP2: Combine the filtered data from every sheet into a single excel sheet.
STEP3: Find the number of rows.
Could you please tell me how to loop through each file in the folder and apply filter over each. Basically we want to know if the Step 1 is achievable using POWER QUERY.
If not kindly, suggest any other way to find the solution for the problem statement.

 

[billszysz]

Of course, if it is your first save of query, you can choose where you want to upload the result.

 

[Chandna]

Our workbook has a two columns.

 

[Chandna]

By default a new sheet use to get created whenever we close and load the query.
This time we are facing the issue when we are selecting the data from the current sheet instead of an external source.
Save n load is not loading the output into a new sheet

 

[scottsen]

If you have that much data, I certainly be loading it into Power Pivot instead of a table.