Excel Magic Trick 1336: Power Query: Import Big Data Text Files: Connection Only or Data Model?

Download Files:

See how to use Import 10 Text Files and Append (combine) then into a single Proper Data Set before making a PivotTable Report. Compare and Contrast whether we should use Connection Only or Data Model to store the data.
1. (00:18) Introduction & Look at Text Files that Contain 7 Million Transactional Records
2. (01:43) Power Query (Get & Transform) Import From Folder to append (combine) 10 Text Files that contain 7 Millions transactional records.
3. (05:07) Load Data as Connection Only and Make PivotTable
4. (08:17) Load Data into Data Model and Make PivotTable.
5. (10:46) Summary

[ElTigreFIME]

It’s magical!
Wow, I didn’t know this was even possible. Now, this approach has unlocked so many doors!
Thanks Mike!

[maksim]

Thanks a lot for Excel is fun channel! 👍

[krismaly]

I enjoyed watching this video and recommend others to watch.

Host seem to be extremely knowledgeable and expert in Excel

He has designed the course I feel extremely good. Worth paying to buy the course.

With this sample video I learned a bunch.

Thanks for educating the community and appreciate your volunteership.

Keep posting some more videos.

Thanks a bunch

[mjprom]

You saved me so much googling! Finally someone explaining these functions in a useful and easy to understand way. Thank you!

[cdsheffer]

Thanks ExcelIsFun. I had a 167 MB txt file I was having issues with even loading. This helped a TON!

[harveyhirst]

My plant manager is going to be so happy our OEE data will take seconds to update and less time to pivot! Awesomee, thank you

[IlaPatel]

Thanks Mike! Love to watch all your videos, always learning so much about Excel and Power Query!

[hrithikzgirl]

Super clear and exactly what I wanted to know. Thanks so much!

[jasonm]

Wow, that's a big size difference. I like comparisons like that. Thanks.

[MrErolyucel]

Clear explanation for the big data problem. Appreciate your existence...

[fokhrulislam]

Yup... That was such a real magic. :) Can't thank you enough or appreciate enough for your work Mike. My Excel journey was started with your tutorials.. :D :)

[CaribouDataScience]

What database is the data imported into when using data model?

[MaiTran-xfop]

Thanks for sharin. I wonder if I just want to choose some in 10 file to refresh, how can I do?
For example for 10 months of data, i just want to fix 7 months and update 3 months. How can I do to save time when refreshing

[zt.]

I am getting on with hard core PQ, Power Pivot activities. The relevant Magic Tricks are great help. (and the entire series on data analysis, of course )Thank you. Am I correct to assume that, with every new version, Excel has intentionally moved towards database management - SQL and the like - to become a greater tool for data management, and go beyond conventional spreadsheet services. (with Lambda, Excel has also reached out to programming - again, unconventional for a simple spreadsheet app.)

[DigitalCraig]

Good Example in difference in file size.

[orta]

Awesome Video - helped me save so much time - thank you

[janardhanvenkatarama]

Hi Mike, I a big fan of you...you are absolutely great in explaining this stuff so everyone (even me) is able to understand it. 🙂
Nevertheless, is there no way to set a parameter in the first step already to import certain columns only BEFORE loading the data into the power query editor itself?
I know for CSV files at least you can select certain number of columns in the Csv.Document command, but is there a similar option to do with Excel Files with Excel.Workbook command which you load from a folder?
I was searching for it since a long time but w/o success at all yet. My issue is that I want to keep this Master Excel file as small as possible as data from the Excel file in the folder is growing everyday I am reading from. Apart from that refresh takes too long time if I first load all columns and then remove columns I dont need in second step.

For instance, I can do that in KNIME very easily but my management does not know KNIME at all... 🙂

Thank you for you feedback.

Regards, Janni

[sajidsherif]

Hi Mike. Thanks so much for the content you put out there - your efforts have helped me big time in my work and I have developed a love for Excel (I knew zilch before watching your videos). I do have a question though. I used the Data Model method as illustrated in the video, but my file size is just over 100MB. I have 132 columns and 1.1M rows. The Pivot Table is working fine with minimal lag, but for my understanding, I am wondering why my file size is so large (can't seem to get an answer with standard googling). Thanks in advance!

[Sal_A]

Loved it! So if we get new data in the original file source and your Pivot Table is based off the Data Model then you just refresh the Pivot Table to get the updated data? Also, what's the Fast Data Load option in Power Query? Will that increase speed for the Connection Only method? Thanks!

[kamranb]

I learn something new everyday with your help.Thanks Mike :-)