Create Excel Master Table from Sub-Tables Using Power Query. Excel Magic Trick 1834

Learn how to take multiple Excel Tables in a Workbook file and create a single master table with all data.
Topics:
1. (00:00) Introduction
2. (00:22) Create Master Table from Sub Tables using Power Query Excel.CurrentWorkbook Function. Learn about pitfalls.
3. (04:39) Recursion Problem, How to Solve.
4. (6:07) PivotTable Report from Mater Table
5. (07:07) Load Directly to Pivot Table to avoid recursion and double refresh.
6. (08:38) Summary
7. (08:52) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #lookup #xlookup #vlookup #vlookupformula, #excelcurrentworkbook

[rikscc]

Wie kann es sein, dass er immer noch neue Tricks findet?

[reng]

THE MASTER MIND ALWAYS GIVING US NEW EXCEL TIPS & TRICKS!! THANKS AMIGO!!!

[johnborg]

Thanks Mike. My first thought would have been the VSTACK. The old school would have been fun too.

[festusraphael]

Another great content here again from my Excel Tutor. Thanks for this free knowledge sharing.👏. More 💪

[garethwoodall]

Appreciate the currentworkbook tips Mike!

[mattschoular]

Thanks for the refresher Mike. Always appreciated

[lucaviglio]

This Is why i love Power Query so much

[SyedMuzammilMahasanShahi]

Thanks amazing Mike for this EXCELlent video.

[SamiullahEhsan]

Greetings! You are doing an amazing work. You are a legit Excel tutor.

Can you make playlists of " Excel for Business Analysis ", and " Excel for Project Management". Your playlists will help alot regarding Excel's dynamic use and formulas in Business Analysis and Project Management.

[TheMarkofZio]

Clever way to append tables quickly! Only thing I would probably change in this scenario is to filter rows containing “OaklandField” to future-proof it

[GeertDelmulle]

Mike, nowadays we can easily append tables using VSTACK.
And, of course, we can simulate the pivot table as well using dynamic array formulas and broadcasting.
So, if we want: all events happen in real time. No refresh required. :-)
And of course, you could do it all in a single cell calculation. Easy! ;-)
I still love PQ-M, though (as you know very well).

[richardhay]

"If you want to do it the hard way" !!?! LOL. I pick the Hard Way Option about once a never!. Great video!! Any difference between Ctl+' and Ctl+D?

[dougmphilly]

I have been doing this for 5 years. I was tasked with creating a dynamic depreciation report covering several affiliates. There are some unique features to each affiliate but the math calculations are the same throughout. Thus, i have multiple tables that I import onto one master table. We no longer use commercially provided depreciation software because the model is good, efficient, and easy to maintain.

[Prosperity-hkub]

Thank you so much, Mike! Can I use Alt A, P, T to convert the Excel table to Power Query? I don't have the key command mentioned on my laptop.

[kevinabrantes]

I use ctrl+D to copy data from cell right above

[ges]

Mike, hi.
It seems that this is what I need but, with formulas.
Could you, please, be so kind as to indicate me the link for the videos you have about it, to make it with array formulas?
Thank you so very much.
Kind regards.

[garyallan]

You would think after all this time, Microsoft would make the default format for dates actually dates instead of date and time.

[billcolton]

Are you able to do a video on using POISSON?

[colin]

Thanks! I ran almost into this identical thing bringing some tables (as ranges) into power BI. But heres a catch, what if ONE of the tables had a column that was not present in the others, but they were structurally identical otherwise? Do you rather create three queries separately and append? Or do you need to start doing joins?

[ExcelInstructor]

Hi Mike, superb video :)
Best teacher ever!
(yes I will always say it :) )

However a question, with this Mcode setup, if some1 creates a sheet regardless of its name, it will be imported, which is bad in this case,
So My question is this, why not make the ffiltering sheets done differently (more robustly)?
You could use Text.Startswith and set it to OakLand,
or
You could use Text.Endswith and set it to ")"
or
you could create a solution that selects only sheets that has number in brackets at the end.
Possibilities are quite unlimited on this.