MSPTDA 14: Power Pivot Into #2: Amazing Columnar Database Importing Millions of Rows Data into Excel

Download Excel START File: https://ift.tt/2OjjG41
Download Zipped Folder with Text Files: https://ift.tt/2yAuGiN
Download Excel FINISHED File: https://ift.tt/2Omigpl and https://ift.tt/2yDQGsS
Download pdf Notes about Power Query: https://ift.tt/2Okxdsa
Assigned Homework:
Download Excel file and complete the homework:
https://ift.tt/2yIVa1J
Examples of Finished Homework:
https://ift.tt/2OoIUxZ
This video teaches about the Columnar Database in Excel Power Pivot Data Model, which allows us to import and hold millions of rows of data in an Excel Workbook and have a small file size. We can also make Data Model PivotTable Reports from this imported “Big Data”.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction: Discussion of how to import “Big Data” Into Excel. Discussion about how Excel, Power Query, Power Pivot and the Data Model Columnar Database work together to make importing big data possible in Excel (and in Power BI Desktop).
2. (03:25) Power Query to Import Text Files (Big Data) From Folder and Append them into a single table.
3. (07:50 Columnar Database Explained. How does Columnar Database work? Where is Columnar Database in Excel?
4. (12:55) Make Data Model PivotTable with Implicit Measures.
5. (14:23) Example of dramatic file size reduction when we use the Columnar Database for a single Text File.
6. (16:21) Define Columnar Database. Lookup at pdf notes.
7. (17:37) Why Microsoft names the Big Data tool in Excel “Power Pivot”.
8. (18:21) Summary

View on YouTube

Further Help

I offer limited consulting services to potentially assist you with data challenges, whether it's designing a complex Excel formula, writing a macro or building a whole new process for data capture, modeling and analysis.  Contact me if you have a need.