Excel Business Analytics Free Course - Forecasting, Linear Programming, Simulation & more. - Basic Excel Business Analytics #38: PivotTable from 4 Million Records with Power Query & Data Model

Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm Learn about Importing Big Data into Excel using Power Query and the Data Model and then making a pivot table from the big data. See examples where the Source Data comes from a text file and Source Data comes from Excel files: 1) (00:04) Download File Information 2) (00:23) Look at 8 text files that contain 4 million records AND look at Excel file with 8 sheets that contain 4 million records 3) (01:18) Import Text files from Folder: Power Query Ribbon Tab (or Excel 2016: Get & Transform group in the Data Ribbon Tab), then From File, From Folder. Importing Big Data into Excel. 4) (01:48) Use Power Query Filter to filter out file types that are not text files 5) (02:02) Remove Other Columns (columns that do not contain record data) 6) (02:10) Expand text files and look at steps to import that Power Query automatically completes 7) (02:26) How to use Power Query Filter feature to filter out “Field Names (Column Headers)” that are incorrectly listed as records in the data set 8) (03:33) Close and Load to “Create Connection Only” and to the Data Model (This step loads the millions of records into the Data Model Columnar database, which is an efficient data storage location and an efficient engine for analytics) 9) (03:56) Look at Data Model Columnar database, which is an efficient data storage location and an efficient engine for analytics. 10) (04:35) Access data Model through Create PivotTable dialog box using the “Use an external data source” dialog button and the “Choose Connection”, Table tab 11) (05:19) Create PivotTable from 4 million records 12) (05:46) Look at how Power Query and data Model reduced file size of 8 25 MB Text files down to an 18 MB Excel file 13) (06:04) Import an Excel workbook with 8 sheets with 500,000 rows of data on each sheet into the data Model using Poewer Query 14) (06:38) Filter out Text file to just import Excel files and then remove unwanted columns 15) (06:52) Add column with Power Function Excel.Workbook to extract data from Excel sheets 16) (07:21) Remove Binary column and expand “GetExcelData” column to expose Data (Excel data), Item (Sheet) and Kind (Table, Sheet or Defined Name) columns 17) (07:37) Remove all columns beside Data Column 18) (07:52) Promote Headers (Field Names) with “Use First Row As Headers” feature 19) (08:02) Filter out Unnecessary Field names listed incorrectly as records 20) (08:22) Close and Load to “Create Connection Only” and to the Data Model 21) (08:41) Build PivotTable report from imported Excel Big Data (Access data Model through Create PivotTable dialog box using the “Use an external data source” dialog button and the “Choose Connection”, Table tab) 22) (09:05) ERROR in Data Type after building PivotTable!!!! See how to edit Power Query query and fix the Data Type error and then re-load field into PivotTable to see that the error is fixed. 23) (10:17) Look at file size reduction after Excel Big Data import 24) (10:28) Summary and Conclusion Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.