How To Combine Excel Tables And Worksheets With Power Query

Рет қаралды 348,605

Күн бұрын

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video, I explain how to combine multiple Excel Tables or Worksheets with Power Query. You will learn how to fully automate this process to save you a ton of time with this common Excel task.
The video covers:
The prerequisites for the data and column structure.
How to setup the connection queries in Power Query, plus a macro to make this faster.
How to append or combine the queries to stack the data.
The update and refresh process when the data changes or you get new data.
How to add new tables to the append query.
You can download the example file on the following page:
www.excelcampus.com/powerquer...
Additional Videos & Resources:
Excel Tables for Beginners: • Excel Tables Tutorial:...
Power Query Overview: • How To Automate Data T...
How to Install Power Query:www.excelcampus.com/install-p...
Introduction to Pivot Tables & Dashboards Video Series: • How to Create a Dashbo...
Free Webinar on The Modern Excel Power Tools:
If you are new to Power Query and/or not sure how it fits into other features like Power Pivot, Power BI, pivot tables, or macros & VBA, then check out my free training webinar. It’s called the Modern Excel Blueprint and it’s running right now for a limited time.
excelcampus.com/blueprint-web...
00:00 Introduction
01:58 Power Query Setup
06:58 Updating and Refreshing Data

Пікірлер: 283

@SuperZlatorog 3 жыл бұрын

Thank you so much!!! I've consulted with so many people in my organization, and no one seems to know how to append tables using power query. Thank you!!! Bookmarked because I'll use this all the time! :D

@gailmckee9474 3 жыл бұрын

Jon, your work is fantastic. I can't believe how much easier this makes my job. Thank you, I have learned so much!

@rafaelperroni8212 2 жыл бұрын

Man, amazing... amazing teaching technique, non-arrogant communication and world class up-to-speed pace. Well done.

@loisscott3932 2 жыл бұрын

Whilst I am generally not a fan of video instructions, this one was great! Jon really explained the concepts well, each step was easy to see and understand why it was done that way. I will be using this to help with a volunteering role and likely will look up some more of Jon's videos the next time I am trying to work out how to do something in Excel! Thank you Jon.

@ExcelCampus 2 жыл бұрын

Awesome, thank you Lois! 😀

@michelrail 2 жыл бұрын

Thank you, Jon, you saved me 2 hours of tedious work per week. I'm beginning to really love Power Query.

@stephanieberget7102 Жыл бұрын

MUCH agreed on the level of communication and explanation. After watching a few other highly recommended channels I was left with many unanswered step questions. Instructors sometimes leave out small details simply because they are so use to doing them they forget how important explaining every step is. So appreciative of how you think and share information.

@fluffyhoneytina 2 жыл бұрын

I was having so much trouble trying to do this merge and you are the only one that explained it and I got it.....no body every mentioned that the data needed to be in a table..no wonder it wouldn't work for me...YOU are the only one that included this information.

@patrickkelley9057 2 жыл бұрын

Great tutorial. Very clear instructions. I only wish I had found your videos before I began working on my current project. Still, this will make moving forward so much easier.

@recildabrown3153 Жыл бұрын

I concur with your previous subscriber. Your communication and visual materials are definitely at a high level! Thank you so much for sharing your expertise. This information helped me much!

@noumanali4847 3 жыл бұрын

Beautifully explained each step. Answered all my questions. exactly waht i was looking for. Thank you much.

@Whatisupbesties 3 жыл бұрын

Thank you so much for these videos you made available and at the reach of everyone who is willing to learn or forced to learn some tools to perform the work as required. My question is if we have different tables with different headers and we need to run a report or pivot table, how can we go with this? Thanks

@ryanjohnson1327 3 жыл бұрын

Love your teaching style! You make a difficult task so much easier. thx!

@wayneedmondson1065 4 жыл бұрын

Hi Jon.. great video! The more I practice Power Query, the easier it gets. Thanks for the examples and practice files.. very much appreciated :)) Thumbs up!!

@khumbulanisikhosana1347 2 жыл бұрын

Thank you so much this helps a lot...copying and pasting was so tiring and boring

@RickWilliamsPlus 4 жыл бұрын

Thanks Jon! I'd explored this myself, but didn't get why I had to double the sizeof the workbook by having the linked tables back in new tabs! With the tip on Create Connection Only you've got me past that hurdle! Thank you!

@ExcelCampus 4 жыл бұрын

Thanks Rick! I'm happy to hear it. Yes, this is one of those somewhat hidden features of Power Query. It's one of those things that makes sense once you understand it, but the terminology and workflow of PQ could be improved IMO.

@conorjordan19 2 жыл бұрын

Hey Jon! Your communication skills are definitely on a high level. You didn't drag on or move to quickly but were able to still make your points. I do have one question. I've been googling and researching and all kinds of things to be able to pull in data from sold ebay listings to an excel spreadsheet for a price guide for my card collection. Is there any advice or help on that you could give me? Again great work

@reyesduran79 Жыл бұрын

You can see if they have APIs available. They just started a new section called "My Collection" or something like that. It keeps track of your collection and current value.

@scottgaines2677 2 жыл бұрын

Another great video! Very straightforward and easy to follow. Thanks Jon!

@whiplashpaint Жыл бұрын

FINALLY!! An effective and simple way to make the Master List!! THANK YOU 😁😁 Very easy to follow.

@meBuck76 4 жыл бұрын

Great video. I took Mynda Treacy's course on Power Query so I knew the basics. This is powerful! Thanks. Really looking forward to your promised video on developing a macro to automate creating table connections in PQ.

@ExcelCampus 4 жыл бұрын

Thanks Mark! I'm working on that video. You can download the file that contains the macro now on the blog post. www.excelcampus.com/powerquery/power-query-combine-tables/ There is a download in the section that explains the macro. I hope that helps. Thanks again and have a nice day! 🙂

@richardmeijer1866 4 жыл бұрын

Hi John, great video! I like the way you present the features of PQ. Very hands on. I’ve got a question: I have to combine a lot of Excel sheets, but sometimes the headername changes or the order of the columns. Before I combine the files I use a macro to copy all the headers to a new file. Then I check the headers visually in that new file which I have created with the macro. Is there a workaround for that in PowerQuery? That would save me a ton of time. Thank you, Richard (From The Netherlands)

@yulpereira3881 3 жыл бұрын

Thanks Jon, this is awesome, just saved a few hours per year of my life

@babakdogaheh7560 Жыл бұрын

I don't know how to say my extremely appreciation for your video . Man, you are really next level . Thanks a million .

@ExcelCampus Жыл бұрын

So nice of you, Babak! 😀

@emadrizkalla159 2 жыл бұрын

The most easy explanation i found, thank you so much great work and i subscribed immediately so i do not miss any of your videos in the future

@mouldydonut123 3 жыл бұрын

You've made what I thought was impossible, possible. Thanks

@michaelbelow1415 2 жыл бұрын

This is a very clean and smooth process in power query and demonstrated very clearly! One question--If I have mult spreadsheets saved out in a share drive folder can I still attain this combined approach into one table or do the table need to exist within one workbook?

@rickcoleman 2 жыл бұрын

Thank you, this has saved me a lot of time. You've made my job a lot easier.

@michellguzelgul4267 9 ай бұрын

This is the perfect kind of content. Straight to the point, clear and concise, no added nonsense. Thank you tremendously.

@ExcelCampus 9 ай бұрын

Thanks for the feedback Michell! 😀

@cli3817 2 жыл бұрын

Very clear and quick demonstration of power query which helped me understand this tool better.

@kalkwok483 4 жыл бұрын

Clearly presented and very helpful! Thank you.

@Men_dude 2 жыл бұрын

Hey Jon, Thank you so much, the information was very helpful and am now able to use the power query in less than 2 minutes. good communication skills.

@anamariaanyana9552 2 жыл бұрын

Thank you so much! This was very helpful and I CAN'T WAIT to apply it at my job! So useful!

@djdtor 2 жыл бұрын

Thank you for the tips this is exactly what I was looking for and I was able to catalogue my company's overstock items so we can efficiently push their sales. You da man!

@abdul-azeezidowu9302 2 жыл бұрын

Awesome ,, simply Awesome . Love your teaching style - many thanks .This is a game changer for me as i manage multiple data from different source ..

@Kiranck1 2 жыл бұрын

Hi Jon thanks 🥰 for sharing your valuable informations to everyone.i really happy after watching this video. you have saved lots of hours from my daily work.god bless you Jon ❤️

@michaelcward1 3 жыл бұрын

Thanks Jon, this is by far the best video out there! Question, if I want to create a master sheet with prices to populate into other sheets in the same excel document, how do I go about it?

@AndreaUK1973 Жыл бұрын

Ow boy! I so love PQ! Loads!! I wish I could work only with this all day long! It makes my brain gain !! And I Loved your tutorial! I’ll stick around for more Loves Gain !! Thank you lovely! You’re such a star!

@mikecircelli9105 Жыл бұрын

The man, The myth, The Legend, Thank you

@doreentetteh869 2 жыл бұрын

Thank you so much, this was very helpful and thorough. Saved me a ton of time!

@amberjen2749 3 жыл бұрын

Thanks Jon! Your video tutorial are great. I'm fairly new to Power Query and currently practicing this great tool. In your sample how would you have Power Query turn the cell in "Category" header to column? For example: Candy column, Fruit & Veg column, Beverage.... I hope you can do a video on this. THanks.

@freshgirl604 Жыл бұрын

super easy to follow and was able to accomplish my task with your help! thank you !

@tabban21 3 жыл бұрын

This is exactly what I was looking to do! Thanks so much

@tomdarling-fernley3178 3 жыл бұрын

Brilliant tool, brilliant tutorials. Thank you! How do you deal with really nasty Excel sheets? Example: a team updates a single worksheet with weekly figures for one year in a range above or below a range of equivalent figures for another year, and you need your query to interrogate both ranges (which are not tables). How does EPQ assimilate a new 'sheet row' of weeklies added in between the ranges?

@maxthepiguy3658 Жыл бұрын

This is how you teach something. Excellent video!!! Thank you!

@sherryizzie5309 4 жыл бұрын

Great tips. Looking forwards for the separate video regarding the Macro:):

@ExcelCampus 4 жыл бұрын

Thanks for the vote Sherry! It will be out soon.

@paulhartline7886 3 жыл бұрын

@@ExcelCampus did it come out yet? I couldn't find it! Thanks!

@TalorTikva 2 жыл бұрын

@@ExcelCampus Looking forward for it aswell

@aviroy7085 4 жыл бұрын

It's a magic Jon !! Thank you for the video. Can you please share the video link for "connection of sheets" with the help of Macro

@user-tm1eq8rz5s 3 жыл бұрын

thank you very much. this is the clearest video I have watched about power query!!

@solobi2010 Жыл бұрын

THANK YOU VERY MUCH JOHN FOR IMPROVING MY LEVEL IN EXCEL

@patrickquinlan5292 8 ай бұрын

I truly appreciate this instructional explanation. Extremely well done! Thank you!

@ExcelCampus 8 ай бұрын

Glad you liked it! 😀

@darrylmorgan 4 жыл бұрын

Wicked Tutorial!Power Query Is Simply Awesome..Thank You Jon :)

@ExcelCampus 4 жыл бұрын

Thanks so much, Darryl! 🙌 I agree with PQ's awesomeness!

@InsightfulJourney123 3 жыл бұрын

Thank you! Amazing teaching!

@dpecoraro66 2 жыл бұрын

Hi John, is there a way to merge sheets with a common field such as ID so that all of the data fields are in the same row as the initial ID?

@erwinvanrheenen3276 2 жыл бұрын

Thanks for the clear and good explanation. You're the best! Best regards, Erwin

@JohnOvens 4 жыл бұрын

Excellent - great tips. Thanks Jon

@Jenicek25 3 жыл бұрын

Thanks for clear explanation! It's very helpful!

@franciscojrlarita441 3 жыл бұрын

Thanks a lot. This video makes my job quicker. then Good Job for you too!

@NestorAdrianzen 2 жыл бұрын

Awesome! Keep it up, Jon!

@deepanadhitya6455 Жыл бұрын

Thank you very much for this video Jon. It is very helpful and insightful !!!!! 🙂

@shraddharangari 3 жыл бұрын

Thanks helped me a lot looking forward for a macro

@Ursula_33 Жыл бұрын

Simply outstanding teaching. Clear, concise and extremely helpful

@ExcelCampus Жыл бұрын

Thanks, Ursula! 😀

@rubychua1283 3 жыл бұрын

Thank you. I find your explanation very clear.

@johnsolis6255 Жыл бұрын

Thank you so much, this really helped me out on an ongoing project I am completing at work!

@AutoDFensa 11 ай бұрын

This tutorial is exceptionally helpful, perfectly explained, and well-produced -- fantastic work, thanks!

@ExcelCampus 11 ай бұрын

Glad it was helpful, @AutoDFensa ! 😀

@EdsonNascimentoEN 3 жыл бұрын

Great tutorial thanks Jon.

@PrairieDogster 3 жыл бұрын

Thank you, very clear & to the point. I am now subscribed.

@taniavincent8455 3 жыл бұрын

Extremely helpful thanks! answered all my questions! All the other tutorials i could find were for Windows 16 which I dont have.

@herbao36 2 жыл бұрын

Thank you for this! Really helpful! Quick question - how do you add the rep name column?

@diegochavezsoria 3 жыл бұрын

That was awesome! Thank you so much!

@nonoobott8602 4 жыл бұрын

This is so helpful, thanks so much

@Vinay6 Жыл бұрын

You rock! I learned an awesome technique in just 12 minutes :) Thank you so much!

@ExcelCampus Жыл бұрын

Happy to help, Vinay! 😀

@wissamaljuburi 2 жыл бұрын

Hi Jon, awesome video man! Very useful!

@robinrogers7949 3 жыл бұрын

Thank You!! Great video tutorial.

@luisfernandocuestasanchez4343 3 жыл бұрын

God bless you man ¡¡ you are our saviour

@jaliali84 4 жыл бұрын

Awesome, simple and powerful

@hectorrapirap663 3 жыл бұрын

Thank you. Verry nice presentation of merging sheets.

@sonnybr4711 2 жыл бұрын

Thank you for saving my day!

@nischithp8378 Жыл бұрын

Thank you so much, using this now I can easily manage my projects

@NavyRescueSwimmer2 3 жыл бұрын

Thanks for posting! If I wanted to add a column to the large query table, how can I get it to update the individual tabs? I know we can refresh if we revise the individual tab to update the query table, but how do we do the opposite?

@andrearagao5489 2 жыл бұрын

Super useful! Thank you

@rrrpandey 3 жыл бұрын

Very nicely describe , Easily understandable Thank you Very Much

@vsnduos217 3 жыл бұрын

Perfect, thanks a lot!

@ShimadaConsultor 2 жыл бұрын

Thank you!

@smitchell2913 4 жыл бұрын

Hi, thank you for this. Power Query is a great tool, and your video is super. However, I have a question.I use power query to compile my annual bank statement from monthly downloaded .csv files from my bank. The power query doesn't seem to cope with credit values in the .csv file and I just get a blank in the compiled spreadsheet. I have put the values back in manually, but then on a refresh to add in the latest monthly statement, those manual values get wiped and replaced with blanks again. Can you please suggest how to overcome this issue?

@katherinealindogan-tagudin7065 3 жыл бұрын

Such a big help :) Thank you.

@tonic4834 10 ай бұрын

Great tutorial!

@sisteradmn 2 жыл бұрын

perfect - got it right the first time - THANK YOU

@funese102 3 жыл бұрын

Thank you so much, the tutorial is awesome

@robertjcliu 3 жыл бұрын

It is helpful. Thank you.

@jessietaber2179 Жыл бұрын

I have to master this. Determined. Simplify my task of auditing my data

@heldercosta5179 3 жыл бұрын

OK I followed along and tried it out with my own data and I was able to make it work. Now lets say that there's monthly data to be added to each table, what can i do to automate that process as well? Thank you for the great tutorials ;)

@seamushand8439 3 жыл бұрын

Very well explained. Thank you

@SandraEverett 3 жыл бұрын

Thank you. Each month I have a new file that I'd like to append to a running historical file of previous months. If I get data from a folder and merged all my historical files together, can I just append a master file with the newest months data by adding a new file to my linked folder rather than remerging all the months together every time?

@ferreiraferreira7392 2 жыл бұрын

Ең жақсы KZfaq

How To Combine Excel Tables And Worksheets With Power Query

Пікірлер: 283