10 Million Rows of data Analyzed using Excel's Data Model

The Excel Data Model (also referred to as Power Pivot) can handle millions of rows of data and can replace the need for millions of LOOKUP formulas

Link to download CSV Files and Demo Excel File

00:00 Intro
00:42 Using Power Query to Consolidate files from a Folder
03:40 Changing Data Types to reduce the file size and improve performance
05:40 Recommend 64 Bit Excel
05:50 The Data Model and creating relationships - avoiding100 Million Formulas!
07:00 Creating a Pivot Table / Pivot Chart
07:45 Adding a Slicer
08:06 Presenting the data in a 3D Map / Globe visual.

Did you know I've written a book "Power BI for the Excel Analyst"?

Connect with me

[kleb]

Wow! You have changed my understanding of Excel capabilities!!!
I used to use MS Access to do what you did with Power Query in seconds. I wonder if Access has much use still!
Brilliant video: super clear, concise, well produced and useful!
Thank you!

[JTFSIX]

OMG, I am sitting here Wide-Eyed and Overwhelmed with Joy...Thank you so much for have been racking my brain on how to reduce and control our data to a professional and organized manner!...AMAZING!!!

[demris]

I've seen this a few times before, but no one has ever drilled down into the performance ROI... thank you, this is going to help a lot!

[IvanCortinas_ES]

This is a powerful example from the Big Data era.
Amazing video! Thank you Wyn!

[maynguyen]

The 3 tips in Excel video took me here. Thank you so much for the short but very informative tutorial video. I was trying to compare almost 9, 000 rows with a couple hundreds rows, and Excel kept saying “Not responding.” I used the transpose function which is very very helpful without knowing it’s Power Query.

[hoppers]

Amazing!! Working up a prototype model in MS Access but wanted to report it in excel with pivots and slicers. Was ready to give up and hand it over to the BI developers but tried your method. 6m rows and extremely responsive. Also used your whole number trick and file size only 22mb. Thanks heaps!

[aidaschart]

I can't say how much I am thankful for this video, i struggled for two days and everything I tried end with not responding, you save me thank

[byregowdahanumanthagowda]

Short and precise explanation. This is amazing and thank you for providing data set for practice.

[alvinestrada]

I’m currently working on +11k rows of data and currently experiencing challenges. Glad I came across your YT channel 😃

[bhuvaneshbabu]

Just amazing.. Your decimal rounding of to reduce space ... worked brilliantly for my data model. Data model pull from SSAS. Thank you so much.

[jazzista]

forgot about the decimal trick. That was awesome . So just by trimming the decimals to rounded numbers, you can reduce your file size. I am going to try that at work

[shrirajdeshpande]

What a beautiful way of explaining how to handle big data. Loved this !!

[tsdsummfiles]

Thank you very much Access Analytic for sharing your knowledge in handling big data using ower query ..God bless

[anthonyfernandezgonzalez]

Thanks, lesson completed. Greetings from Costa Rica.

[bernielindsey]

Wyn, your content is always helpful and timely. Can you comment here about how to best use the same dataset but doing the analysis in Power BI? Since you don't have a Table, what is the connector with PBI?

[thuhangtran]

Very well explained and easy to understand. Thank you.

[phildanley]

Thank you so much Wyn for this video. I was able to take a 132 MB report and consolidate it down to just 9 MB! Now everyone in my company who does not have Excel 365 can view it as well as slice and dice.

[roberth.]

Well done. Thank you for the instruction.

[phoebefleming]

This is fantastic. I feel like I have a new superpower. Great explanation video.

[JamestheZinHondo]

This is helped me out TREMENDOUSLY! Thank you!