Handle millions of rows in Excel - Large slow files? - use Data Model

#excel #datamodel #analytics #efficiency365
Import data into data model instead of Excel sheet.
No limit on number of rows. Small file size. Fast analysis using Pivot Tables, Pivot Charts and DAX formulas.

If you do not have Power Pivot menu, refer to this article:

If you do not see Data - Get and Transform, you may need to install the Power Query Add-in.

Contents
00:00 - Introduction (the problem)
00:17 - The solution - data model
00:46 - Benefits of data model
00:57 - Import data using Get and Transform (Power Query)
02:22 - View the data model
02:43 - Import one more table
02:59 - Create relationship (instead of Vlookup)
03:24 - Create Pivot Table from Data Model
03:51 - Create Measures
05:25 - Using Measures
05:42 - Importing more than million rows
06:36 - Check large data Pivot Performance
08:41 - Add local table to data model
09:25 - Related topics
09:47 - Summary

Buy Dr Nitin's latest book
-----------------------------------------
Efficiency Best Practices for Microsoft 365
130 best practices everyone must know
Work Smarter, Grow Faster

Dr Nitin Paranjape is the pioneer in maximizing work efficiency in a modern work scenario.
Dr Nitin's Office Masterclass
----------------------------------------------

Udemy Courses by Dr Nitin Paranjape
--------------------------------------------------------------

Learn more from 1000+ blog articles by Dr Nitin Paranjape

Production
--------------------

[niteeshmishra]

Powerquery and Powerpivot is Cornerstone of These two powerful tools make my job a lot easier.

[Pelasomma]

You sir, are a saviour! My dashboard was getting slow and clunky due to how much data it had, now it runs quicker than it ever had before!

[fernandogaudenzi]

Dear Dr Nitin. Thank you very much for your explanation. It was finally the solution I needed to consolidate the data from historical data in separated excel files (abou 3 million records) and analyze them all together. Thanks again!

[RameshTim]

Excellent presentation. Exactly the solution I was looking for.

[CalebHarrison-fzlu]

This is awesome. Thank you for the video

[vijayarjunwadkar]

Thanks a lot Dr. Nitin! While I am already using these tools, it's first time, I could see large data model in action and the way you have explained and demonstrated is excellent! Looking forward to your inputs on using DAX soon. 🙂👍

[ghargettCarolina]

I need to filter rows by evaluating whether a text field used for notes has a particular text string in it. How might be best to do that?

[Kuttypisaasu]

hi sir, what if i have a big file for every day and i created a final file with the above instructions, and then i wanna add new files into it, will the final file automatically pull data from the new file i added in that folder, example, i already have 29 days file and i added 30th day file after creating the data model file?