digest
Short Summary
Main recommendation: For Big Data text files, load the Power Query result into the Excel Data Model rather than using Connection Only, because the Data Model's columnar storage produces a much smaller file and faster PivotTable building and refreshes. The video demonstrates this by importing about 7 million transactions from 1,335 tab-delimited text files, then comparing the two approaches side by side.
Key Points
- Connection Only is fine for small data sets, but with millions of records every PivotTable action forces a slow refresh of the underlying data.
- The Connection Only workbook grew to about 29 MB because the PivotTable cache held all the data, while the Data Model version was only a little over 1 MB.
- Loading into the Data Model uses a columnar database, which stores large data efficiently and speeds up querying and PivotTable creation.
- Connection Only has one benefit: you only refresh the PivotTable, not separately the Power Query import, which updates both together.
Details Worth Noting
- Concrete instructions:
- In Excel 2016, go to Data > Get & Transform > Power Query > New Query > From File > From Folder, click Browse, select the text file folder, click OK, then click Edit.
- Name the query "All Text Files in Proper Data Set."
- Filter the Extension column with Text Filters > Equals > ".txt" to keep only text files, then right-click Content and choose Remove Other Columns.
- Click the double downward-pointing arrow on Content to combine the files, then promote headers.
- Because only the first file's headers were promoted, click the Website column filter, choose Load More, and uncheck the stray "Website" value so those header rows are removed.
- Keep only Website, Product, and Quantity: hold Shift, click Quantity (selecting the range from the first needed column through Quantity), right-click, and choose Remove Other Columns.
- Confirm data types (text, text, whole number), then choose Close & Load To and select "Only create a connection." For large data sets, choose "Add this data to the Data Model" instead.
- In Excel 2013 or 2010, Power Query must be downloaded and installed as a separate tab.
- The source data is 7 million transactions across 1,335 files, with each file around 46 MB, which is what makes this "Big Data."
- Dumping a single table into the Data Model does not require Power Pivot to be enabled to access it, but Power Pivot lets you view it; the imported table cannot be deleted from Power Pivot and must be deleted through Power Query.
- The PivotTable built from the Data Model is identified by a small black line icon in the field list, confirming its source.
Bottom Line
For large datasets, load into the Data Model to keep files small and PivotTables fast, and reserve Connection Only for smaller data where its refresh convenience matters more.
