Create Excel Master Table from Sub-Tables Using Power Query. Excel Magic Trick 1834
Вставка
- Опубліковано 13 лип 2024
- Download Excel File: excelisfun.net/files/EMT1833-...
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
Wie kann es sein, dass er immer noch neue Tricks findet?
Denn das Schöne an Excel und Power Tools ist, dass sie unendlich sind. Solange du weiter suchst, wirst du immer neue Tricks finden :)
Becasue the beauty of Excel and Power Tools is that are infinite. As long as you keep looking, you will always find new tricks : )
Question translated: How is it that he's still finding new tricks?
THE MASTER MIND ALWAYS GIVING US NEW EXCEL TIPS & TRICKS!! THANKS AMIGO!!!
You are welcome a lot, My Friend!!!
Thanks Mike. My first thought would have been the VSTACK. The old school would have been fun too.
But what about when you get a new table?
@@excelisfun if you get new tables, Power Query is the way to go. True!! :) :)
Another great content here again from my Excel Tutor. Thanks for this free knowledge sharing.👏. More 💪
Thanks for the more power!!!
Mike, biggest thanks for your time
Biggest you are welcome!!!!
Thanks Mike, another great video!
You are welcome for the refresher, O Smart Excel Guy Roy!!!!
Appreciate the currentworkbook tips Mike!
You are welcome for the Excel.CurrentWorkbook tips!!!!
Thanks amazing Mike for this EXCELlent video.
You are welcome, Fellow Teacher : ) : ) : )
Thanks for the refresher Mike. Always appreciated
You are welcome, Excel Master Matt : )
This Is why i love Power Query so much
LOVE = Power Query!!!!!
Impressive!
Rt click g is AMAZING!!
Thanks Mike. Great video.
Thanks, OWRH, that is like OG, but Original World Record Holder!!!!
Thanks mick sir, excelisfun❤❤
excel is fun : ) : ) : ) You are welcome!
PQ rules!!! Thanks Mike!
it's god's gift to number crunchers
Well done, thanks
You are welcome!!!
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).
True... But add new Excel Tables and it is easy to include with Power Query. It is not that it can't be done with dynamic arrays, at least masters like you and Excel Lambda could do it, but us mere mortals have to do it the Power Query way lol
@@excelisfun LOL! Only the thing is that we learned all that from YOU.
Crazy array formulas, dynamic style. You were the very first to show us. :-)
Having said all this, I personally have experience with both, and although dynamic arrays (DA) recalculate in real time, it is impossible for the ordinary users to modify those formulas if needed. So, switching back to PQ-M, the data model and pivot tables is a more agile and flexible strategy. Then again if management needs a tool for what if scenarios in a complex real life context, custom built DA-solutions are the most effective way to go. The right tool for the right job, I guess.
@@GeertDelmulle Said so well: right tool for the right job!!!
Very nice video Mike :) interesting
You already knew this, though : ) : ) : )
@excelisfun I know that all your videos are super interesting, and I try to not miss any but I also like to comment on all your videos:) because u r doing a super amazing job and this is the least I could do. I wish I could do more :)
@@nadermounir8228 What you are doing is perfect : ) I love all your comments!!!!!
You would think after all this time, Microsoft would make the default format for dates actually dates instead of date and time.
Right!
I think PQ was developed by the SSRS team. They’re off the database world where by default dbs use date/time.
@@rjbush7955 , Thanks for this great info : )
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.
Watch my 2 min intro video that shows all I have at youTube classes. I do have a Busness Analytics playlist and much more:
ua-cam.com/video/GNhN1Zw8oM0/v-deo.html
Home page withh 10 best playlists: www.youtube.com/@excelisfun
@@excelisfun Many thanks for your reply
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
I like it! Good idea. Plus, I like your new term" "future-proof it" : ) : ) : )
Hi Mike, This is great to see your videos. Thanks a lot.
I got a query.
I am creating a payroll program in Excel, and need to change the date from a single cell in one sheet (say cell G2 in Settings sheet), so that all the other sheets have the same date (in cell G2 in each sheet) and so the results.
But I have to move each time to that Settings sheet and the cell G2 to change the date, and go back to see the specific sheet, like attendance sheet, overtime sheet, etc. to see the result data of that month.
Is it possible to change the date from any sheet (cell G2 for all sheets) and the rest of the sheets have also changed the date?
Regards
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.
Alt A, P , T is the correct keyboard. Also, "convert" is not the right verb in "convert the Excel table to Power Query". Instead you would say: "import the Excel table into the Power Query Editor", or: "load an Excel table into the Power Query Editor" : )
Thank you so much for your kind correction and insightful suggestions.😊🙏 @@excelisfun
: ) : ) : ) : ) @@Prosperity-hk8ub
"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?
Yes, Ctrl + ' copies item in cell above, Ctrl + D fills down.
That is a clever way of wording it:
I pick the Hard Way Option about once a never!
: ) : ) : ) : )
But I do not understand what you are communicating. Are you saying you never use Excel.CurrentWorkbook, or never store data backwards (sub to master). Because yes, proper table structure would require that we keep a master and then if we need subs, we extract. But guess what, so many people in the world do it this way. For example, read the great accounting depreciating example that dougmphilly posted in the comment above.
@excelisfun I do like Excel.CurrentWorkbook and I definitely keep a master but VSTACK is my default option. I would only go to PQ if I were already needing it for other transformations or if there was a high frequency of new tables. So far as Ctrl+D js concerned I use it all the time and seem to always need to copy either the data or the calculation from the cell above. So Ctl+D always works for me. My comment regarded my desire to avoid methods that predate PQr VSTACK. No desire to turn the clock back to 2010 or even earlier!
@@richardhay645 For a single cell Ctrl + D and Ctrl + ' do the same thing.
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.
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?
It depends. Maybe you exclude the rogue column in the worksheet before importing, maybe you import one at a time and remove the column before appending, or maybe you build a custom function to deal with rogue column, as taught in MECS M Code video #12 and #13: ua-cam.com/play/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW.html
@@excelisfun thanks for the response! Plot twist...I'd like to keep that column. Is it retained by default? And then the other tables would just merge with null data there?
@@colin8601, yes, keep column and nulls. That is often what we want.
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.
That is a great example!!!! Power Query knowledge is a must for Accountants. They should task for it on the CPA exam!!!! To bad most accountants in the world have no idea what Power Query is : )
@@excelisfun in the future, CPAs that cant use PQ/BI will be cashiers. anyone that can will get an executive suite, CPA licensed or not.
@@dougmphilly , it has always been a shame, even back in 2002 when I passed CPA in California, that the vast majority of accountants don't even know basic Excel very well. It is the fault of academia. Everywhere on the planet, from back then to now, education doesn't emphasize Excel and Power Tools enough. Here at Highline College, if you get a business/ accounting degree you have to go through at least 4 of my Excel and Power Tool classes. As a teacher, I fulfill my duty to train people properly, but alas... it does not seem to be doing very much good for the full planet lol
@@excelisfun you just hit a nerve. i was a dual major at drexel - accounting & systems management. bored out of mind most days but a summer course in corp tax at 8AM, lucky if they ever saw me half of the time. so i pop in one morning and the prof asks me about the tax rate for something. i said i can't recall. ok, not the most professional response. but his response was that people who do not memorize these tidbits will not do well. i wanted to say given my training in COBOL that people that write programs that gives them timely information, no matter how trivial ie. tax rates for railways or something like that, will do quite well. back in 1983 when i graduated, you were evaluated on how fast your thumb could hit enter on a 10-key calculator. anybody care now?
I use ctrl+D to copy data from cell right above
Cool thanks. Ctrl + D is copy Down, one or more cells. Ctrl + ' is copy cell above - just one cell.
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.
Thank you for your kind words, My Rad Teammate!! Excel.CurrentWorbook does not import worksheets. However, your idea of a robust name convention to then use for filtering is a really good idea.
@@excelisfun yeah, sorry I spoke hastly,
I know Excel.CurrentWorkbook imports only table, filtered ranges, named ranges etc and not sheets themselves,
But with what im dealing in work some1 will always find a way to break the code :D
So thats why I ask about the filtering objects within specific sheets.
and in addition some1 can create something on those sheets aswell...
Naming conventions are the way to go : )@@ExcelInstructor
Hello Mike, where can I purchase your book regarding power query and a video samples
I juts wrote "The Only App That Matters" book, but covers everything: Excel, Power Query, Power Pivot and Power BI: www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700
If you want files that go with this video, the lnik is in the Show More link below video.
Is there any way to do the same job but in excel 2016?
Are you able to do a video on using POISSON?
I already have five videos on just that function lol
Anytime you need something, on my UA-cam homepage, search my channel. I have 3,600 videos so I have most of what people need. Here is one good video on POISSON:
ua-cam.com/video/Y8qrtjnltKU/v-deo.html
Dear sir,
Can you tell me MECS video series is ended??
Yes, it has needed. There are 24 videos in a 22 video class at this playlist (there are 2 extra videos that make 24):
ua-cam.com/play/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW.html
What version should i download for Microsoft excel 365 thanks.
Microsoft 365 only has one version, forever. This is on of the beautiful aspects. The version just updates each month.
Whatever you do, do not get Excel 2019 or Excel 2021 - they do not have many wonderful features that M 365 has : )
Good video. I have a slight alteration to request. How do you append new data to an existing Excel table and NOT create a new consolidated table as a result? Every month i extract bank data from various financial institutions and run them through (ETL them) power query. The result is a table with just the current month's data. NowI just want to add the new month's data to an existing table that consists of all the previous month's but the resulting consolidated data (old months plus this month) must retain the same historical consolidated table name because a series of dashboards are based on that historical consolidated table name. All of the examples i have seen create a new consolidated table. If you have already addressed this issue please point me to it. Thanks for your help.
I guess I would create new tables because it is a historical thing - need to leave a track record. But I guess you could add a dynamic filter or something. I have no video on this. Plus, I am still unclear on exactly what you are trying to do.
If I correctly understand what you are asking, it should be easily possible to import the new data, run it through your ETL process and then append the resultant (cleaned tables) to the bottom of your historical query assuming the data structure is the same in all tables, thus when refreshed the table would be extended and still retain it's name.
If your historical data is not in the form of a query, you could always import the historic data as a new table, though you would have to ensure that you only bring it the historic data in once and then overwrite the historic table with the new data. I would suggest you try your code on a copy first. It may be a better idea to copy your historic data to a seperate file kept in the directory with your new data files so you don't risk reading the historical data in several times and then overwrite you datatable with the new table.
Worth re-iterating: DO THIS ON A COPY AND TEST IT UNTIL YOU ARE SURE IT IS NOT GOING TO DESTROY ANYTHING! and ensure you have a backup of the original file.
Thank you for replying. The reply from RoyWilson below accurately summarizes what i am attempting. I'm just bringing in monthly data, scrubbing it, and appending it to all the previous monthly data creating a growing historical data base of financial data (bank statement data). All of the records scrubbed and the historical data have exactly the same layout. Roy's suggestion of taking the new month's scrubbed data, output it to a new month table then cut/paste into the historical table which is what i am doing now. I just thought there was a way of doing all within a PQ script.
Thank you, your assumption in the your first paragraph is correct. By "append" i assume you mean cut/paste from the new month table to the bottom of the historical data table.@@roywilson9580
@@meg-a-bytes I think I understand: you have a new file each month? If yes, then the from folder feature is the perfect way to do this.
What if the three tables are in three different sheets?
That is the example I showed in the video : ) It does not matter where the Excel Tables are, or how many new ones you add later!
I've used the wrong terminology. I want to combine some numbers of excel files inside a folder. In your example you have the three table in the same file. In my case i have 3 files inside the same folder.
Ok, i've found my answer inside the amazing MECS04
I meant the .netframe work......
I do not understand.
A 001 9
B 002 12
C 003 9
D 004 7
A 001 5
E 005 4
F 006 7
G 007 7
B 008 2
Now this data i want to merge how..
A 001 14
B 002 14
C 003 9
D 004 7
E 005 4
F 006 7
G 007 7
See the difference.. how to do in excel