Replace 3 Way Lookup Array Formulas for 1.2 Million Rows of Data
See how to improve spreadsheet performance (calculation speed) by replace a workbook with array formulas to do 3 way lookup for big data by using Power Query and PowerPivot:
1. Using Power Query import three data sets from 3 Excel files that contain 1.2 million rows of data. Use Append feature. Import lookup table from 4th Excel file.
2. Load data to PowerPivot Data Model.
3. Create Concatenated columns (join columns) to create a unique identifier (primary Key).
4. Create Relationship
5. Create Pivot Table Report that simulates three way lookup in order to create summary sales report with categories from one table and sales from a second table.