Excel Magic Trick 1436 Excel 2016: Combine 2 BIG Tables into 1 for PivotTable Report, No Power Pivot

Download File:
See how to use Excel 2016 to take a 900,000 row table and a 1,000,000 roe table and combine them into one in order to make a PivotTable Report. See how to use the Power Query (Get and Transform) Append feature to combine the two tables and the load it to the Data Model in order to create a PivotTable report.

[paulreardon]

I have been looking for a way to do this for 3 days, finally found your video and it immediately worked. Thank you so much!

[OzduSoleilDATA]

This is amazing! The small file size, and the ability to put that massive info behind the scenes. YEAH! This is cool.

[alpeshshah]

Excellent, thank you much, dug me out of a whole and actually learnt something.

[kkhaing]

Thanks for your video! It helped me a lot

[dalskiBo]

Great tutorial, thanks Mike. My was different on Excel 2016; I did not have Get Data in the ribbon nor was it available by customizing the ribbon. There was a Combine menu under the ribbon which I hit - Merge & Combine became available however these they were greyed out.
What worked for me was to right-click in the Query Pane on a table and selected Append from there, hopefully this helps someone some day.

[adebalqadhi]

That's really amazing and helpful video, Thanks a lot

[jimmysoncookland]

Nice thanks for this was having trouble with my file sizes too so double tips :)

[Firesun]

This helped A LOT!! Thank you for posting and doing what you do!!!

[sherifelgamal]

Nice way to work with very long tables!
Also in excel 2010 using powerquery you can create a pivot table from query connection only by choosing "Use an external data source" in the create PivotTabe dialogue without adding the table to the data model (there is no data model in excel 2010), so you can append large tables together in powerquery and summarize in a pivottable, but of course you will not be able to create measures other than what is available in the pivottable by default

[ShyamKumar-tsmj]

Thanks a ton.... Really it is awesome...!!!

[MrWrDs]

Great! I tried a different tuto from 2013 and it kind of screwed the date/month organization. This is perfect, your chanel is very valuable!

[rtrbs]

Awesome Sir...Thank you very much ....One doubt....After adding two tables to Data model and Pivot Table, now I have 3rd Table how do I add that table to same Data model and update Pivot Table...

[kevinmcaleer]

Interesting to see the filesize shrink considerably. I assume thats because Excel only store the data, and not all the cell metadata for formatting, positioning etc. Great Video :)

[malenepontes]

Thank you, This worked perfect I only had one problem. how do I change the Value from count to Sum. It would not let me change it.

[garym.russell]

Thanks for another awesome video. Question, please-- Why can't I insert a function after the combined pivot table is built? For example, I need to apply a percentage to one column in the table but the option isn't available .

[joyzuniga]

This is helpful somehow, how about data with a different header, only one can be similar :(. Is there anyway can combine this?

[Abcqy]

Hi! Thank you so much for the video, it was very informative! When I tried to delete the tables after I have appended them and loaded to data model, the data connect shows an error message that the table is not found. Can I check how I am able to remove this problem? Thank you!

[dhawal]

Great Video. Just one question. Is there a way to use getpivot and get data out of this combined database in other sheet or workbook? Doesnt seem to be working.

[hectorvergara]

This is exactly what I was looking for. We have 12 large tables that need to be merged. However, our office still has MS-Office 2013. How can I do this using Excel 2013?

[roelmangubat]

exactly what I was looking for.
how many(max) tables can I append? I will be working on at least 52tables, each with >100k rows.
Thanks.