Excel 2013 PowerPivot Basics #13: Replace 3 Way Lookup Array Formulas for 1.2 Million Rows of Data

Power Query and Power Pivot example. Big Data example.
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. ( minute mark) 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. ( minute mark) Load data to PowerPivot Data Model.
3. ( minute mark) Create Concatenated columns (join columns) to create a unique identifier (primary Key).
4. ( minute mark) Create Relationship
5. ( minute mark) 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.

Big Data.

[excelisfun]

Excel 2013 PowerPivot Basics #13: Replace 3 Way Lookup Array Formulas for 1.2 Million Rows of Data
Power Query and Power Pivot example. Big Data example.
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. ( minute mark) 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. ( minute mark) Load data to PowerPivot Data Model.
3. ( minute mark) Create Concatenated columns (join columns) to create a unique identifier (primary Key).
4. ( minute mark) Create Relationship
5. ( minute mark) 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.

[harishgupta]

Thanks for the knowledge, I was also able to do the task completely with power query.
1. In Power query loaded all tables, merged the tables and created a pivot table report from power query.

[excelisfun]

rwfrench66 , You could if you want. Some reasons people use excel and PowerPivot: 1) Many people don't know Access; 2) Access does not have a columnar database that is optimized for analytics like the Columnar Database in PowerPivot; 3) DAX formuals and PivotTables have many more calculations; 4) Access is notorious for choking on large data.

[HarryWexler]

Nice! I think the person before you may have known Excel but wasn't big on tech all together. When dealing with so much data you have to keep that in mind.

[canefan]

I love that they updated it to allow multiple tables to be appended at once.

[rwfrenchGenX]

Why not do the query in Access instead of going through all of this?

[YvesAustin]

Mike, Fantastic series!
I have a specific problem loading data fields to the data model when the number is out of range of a 64 bit integer value (6.12976E +19 is not accepted). I am working on a 2.5 million row history table from a parcel shipper (12 individual monthly tables loaded with Power Query exactly per your video) and one field column (FedEx tracking number) has occasionally errors (adds extra zeros) and throws me off when I run Power Query and load the appended tables to the data model. I have 613, 000+ errors which is a lot to correct manually!
What suggestions would you have? (I use MS Excel 2013 with Power Pivot and Power Query)
Thanks a bunch in advance :)

[cristianllamassimon]

Once I've imported the data into the data model, can I just remove the source Excel files?

[ktaoge]

I wonder what's your CPU? why it reacts so fast in your Excel? And thank you very much for your video!

[EVUTube]

Awesome!  I can't wait to sink my teeth into this Power series.  So cool !  :-)

[yahoomain]

Hi, I tried to do the same operations in the MS Excel 2010 (where power pivot and power query is been installed).

1) The power query uploaded the tables. But i was not able to import the tables into power pivot by using "Power Pivot Window ".Where as in your video, you have used the "Manage" menu.

2)In power query ->Close and upload to menu. my file size was less than 100kb.Where as in your video it was nearly 2 MB in size.


Can you please let me know how i have to do the same operation in MS Excel 2010

[maximilianvandergriend]

Is there a way to do this with an imported CSV file?

[ronverheijen]

I know it is Power Query doing this, and I think I can see the logic, but when you go through the steps of getting the feeder tables into PQ, you go "Click Only create a connection, then Click Load", and the screen shows "Load disabled", that may be somewhat confusing. Again not you, but it may be worth pointing out to prevent confusion.

[ahmed]

ExcelIsFun you told me if i have small question to post, i hope its success 
my question is How to replace specific text in powerpivot ( like normal excel ) ?
Best regards

[trungbui]

ExcelIsFun  Hi ExcelIsFun, Thank you very much for sharing. By the way, I was wondering if you have any Excel videos about econometrics.