Importing data from Folder through Power Query

[earthworm]

I have 30 files . Each files contains data of approx 6 Million. I imported the folder through Power query. Then I get and transform data and then I used the formula Excel.Workbook(Content). When I am expanding the column power query is taking too much time in loading file before I move to next step of expanding the column to show data. Is there any faster way ? Because I have to wait for hours before moving to next step. Please advice

 

[Matt Allington]

What file format are the source files?
Are the files/folder on a network drive? If so, move them to your local drive and it should be a lot better.

 

[earthworm]

What file format are the source files?
Are the files/folder on a network drive? If so, move them to your local drive and it should be a lot better.

File format xlsx and location is local drive

 

[Matt Allington]

You say each file contains "approx 6 million". 6 million what? It can't be rows. When you say "I used the formula Excel.Workbook(Content)" did you do that manually, or did you use the UI to combine the files automatically?

 

[earthworm]

You say each file contains "approx 6 million". 6 million what? It can't be rows. When you say "I used the formula Excel.Workbook(Content)" did you do that manually, or did you use the UI to combine the files automatically?

6 Million Rows. I used the formula manually

 

[Matt Allington]

This is not possible in a single Excel sheet

 

[earthworm]

This is not possible in a single Excel sheet

It's working but too slow

 

[Matt Allington]

You are not telling me something. You say your source files are Excel files and you say they contain more than 6 million rows. An excel sheet has a maximum of 1 million rows, so something else is going on. Are the 6 million rows spread across more than 6 sheets in the same book?

 

[earthworm]

You are not telling me something. You say your source files are Excel files and you say they contain more than 6 million rows. An excel sheet has a maximum of 1 million rows, so something else is going on. Are the 6 million rows spread across more than 6 sheets in the sa

You are not telling me something. You say your source files are Excel files and you say they contain more than 6 million rows. An excel sheet has a maximum of 1 million rows, so something else is going on. Are the 6 million rows spread across more than 6 sheets in the same book?

I need a merge a data from each excel file that contains 800,000 on each excel file

 

[Matt Allington]

Here are some thoughts:

1. are you merging or appending? Appending is generally faster than merging but you may not have a choice
2. I suggest you use the UI, not manually written queries. Usually the UI knows best
3. You say it is "too slow" but you don't specify how long it is taking. I would expect such a query to take about 3-5 mins assuming pure append.
4. What is the file extension? if it is XLSX it will probably be faster than XLSB