How handle mass data in Excel (e.g. powerquery) in different sheets per query

[Sensewell]

Hello everyone,

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 (ideal xlsx). 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!

[#2]

@Sensewell The solution is in your last sentence. Connect to each file separately to create 10 queries. Thus, NOT by connecting to all files from a folder.

[#3]

Is there no way to get for each file a different query automatically? Also no option to filter data from one query via Filename on the sheets?

[#4]

@Sensewell Then you could connect to all relevant files in a Folder and just press "Combine". That should automatically add a column Source.Name to the combined table.

Now create a reference to the combined file and filter for the three files from which you want to load the data into an excel sheet. On the Home tab in the Advanced Editor, "Close & Load To..." or right-click on the Query in the Queries & Connections side-bar and select "Load to..." if you close the query earlies as a "connection only". Repeat these steps for the other files. I see no other way.