Power Query to Load Data on Multiple Excel Sheets

[mayankhb]

Hi Friends,

I have more than 10 files in a folder. What I am trying is to be able to load those files in one Excel file (but on different sheets) using Power Query. I know Power Query can combine all these files together and load them on one sheet but that's not possible in this case (because each individual file has around 9 Lakh rows and I want this data row-wise). Is there any solution?

 

[RoryA]

I can't see any real benefit to what you're doing, but you could add an index column to your combine query, then create additional queries that output different ranges of the index values to separate tables.

 

[mayankhb]

Thanks for replying sir. I understand the adding index column part but I don't understand how I will be able to make it dynamic based on the number of excel files. Like there should be 10 sheets created for 10 workbooks 11 for 11 and so on....