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

 

[earthworm]

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

I am appending in power query through formula. I thought binary is faster . Are you sure binary is slower ? . UI suggest is noted

 

[Matt Allington]

I have a vague recollection that Miguel Llopis told me that XLSB uses the ACE Jet Engine (of MS Access infamy) to connect and load the data, and that it's slower. No, I am not 100% sure, but that is my recollection.

 

[earthworm]

In my view and experience binary is faster and smaller in size.