How handle mass data in Excel (e.g. powerquery)

Question

please dont hurt me for this question... I know it is not the ideal way to handle mass data- but I need to try...

I got a folder with 10 csv Files which are always under the xlsx row limitation of 1.048.576 rows.

Now I try to combine those files in one file. The combination of all files reach over 1.048.576 rows. With the import dialog I always get the error saying: not possible to load all data etc..

I found a way to load the data only in the data model of power query and not directly in the sheet. But I cannot find any way to split the data into different sheets.

Ideal split for example:

Sheet 1: File 1-3 Sheet 2: File 4-8 Sheet 3: File 9-10.

Is there a way to get for each file a different query and then to append those queries in the sheets? I would like to get 10 queries, which I can append the way mention above.

Thank you for your Input!

Answer 1

You can load each CSV file separately as a unique query, with each File... Close and Load saved as Connection Only. Then create separate queries that use a Table.Combine() to put together the combinations you need [data .. Get data … combine queries .. Append...] in separate queries that you file load as either tables or pivot reports back on the sheets