How to connect two tables in Excel - With Example Workbook

Подписаться 627 тыс.

Просмотров 144 тыс.

50% 1

When I was working as a HR data analyst, one task I would do every week is combine data in two different tables. In this video, let's look at two ways to solve the problem. One using formulas and another with Power Query ⚡. Both methods are really simple to set up and will save you TONS of time in the long run.
⏱Video topics 👇
==============
0:00 - Combining two tables in Excel - the problem
1:15 - Using lookup formulas to combine tables (with XLOOKUP)
2:18 - Using VLOOKUP to combine tables
3:04 - Using Power Query to join two tables (merge queries)
6:25 - Loading merged data to Excel
📗 Sample workbook:
==================
Grab the sample file from this link if you want to follow along or practice.
chandoo.org/wp/wp-content/upl...
👨‍🎓 Learn more 💻:
===============
📺 RU-vid VIDEOS:
on Power Query (complete tutorial, 80 mins, 260k views) - • Power Query Tutorial -...
on XLOOKUP (intro, 10 mins, 280k views) - • I don't use VLOOKUP an...
on combining data from Excel files - • Powerful trick to comb...
👩‍💻 COURSES:
Power Query mini-course (2 hours, full PQ with many practical examples) - chandoo.org/wp/power-query-po...
Lookups mini-course (1 hour, 13 lessons) - chandoo.org/wp/lookups-for-da...
FULL Excel (24+ hours, 100s of examples) - chandoo.org/wp/excel-school-p...
~
More Power to You ⚡😀
PS: Scene from The Dark Knight at 0:33
#Excel #powerquery

Наука

Опубликовано:

27 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 91

@fairoosk8922 2 года назад

How much I learned from you , more than a mere word of saying thank you!!!

@chandoo_ 2 года назад

You are welcome Fairoos 😀

@keeloraz9452 9 месяцев назад

Batman clip was so inteligently chosen, it fits so well it made me chuckle. Brilliant stuff Chandoo.

@Gunheadchicka 6 месяцев назад

Bravo! Just what I needed to know! Thank you!

@nickfosh2990 Год назад

Thank you for this video, the power query part was something I had been looking for, for months. Keep doing you, big love.

@PurplePegasus2531 2 года назад

I literally have a project that I have to combing multiple tables TODAY and generate a report tomorrow by COB. Bless you sir 🙏 🙌

@chandoo_ 2 года назад

Talk about timing... 😎

@sebastianschell4316 2 года назад

The use of that Dark Knight Scene gave me goose bumps :D Love the creativity!

@ScoJa71 9 месяцев назад

Great tips!! Thank You!

@GurayVural-nz1lk Год назад

Excellent teacher!

@vijayarjunwadkar 2 года назад

Thank you Chandoo! Very helpful tips as usual! 🙂

@chandoo_ 2 года назад

My pleasure!

@danieladlt618 Год назад

You my friend are awesome! Thanks for teaching

@littlenitanyashow1750 2 года назад

Just only one word for you - Fantastic 🥂

@keepitsimpal 5 месяцев назад

Solved a major problem today. Thanks.

@chrism9037 2 года назад

Super helpful, thanks Chandoo!

@chandoo_ 2 года назад

My pleasure Chris.

@rcmasangcay 4 месяца назад

Thank you!

@truewiking 2 года назад

You are such a good teacher 🙏 Thank you very much

@chandoo_ 2 года назад

You are very welcome

@MrPraveenmahesh Год назад

CHANDOO THE GREAT !!!

@GeorgeAJululian Год назад

Thank you very much all your videos are useful and helpful

@gehadabdella8537 2 года назад

YES THATS THE STYLE I WANT OMG

@bibekkoirala5294 2 года назад

Awesome video as always.

@chandoo_ 2 года назад

You are welcome Bibek.

@gagankumarsahu349 2 года назад

Your videos always help me a lot 👏

@chandoo_ 2 года назад

Glad to hear that!

@malaninyirenda1190 2 года назад

Just what I needed to learn

@chandoo_ 2 года назад

AWESOME 😎

@Azhar_Khan383 2 года назад

Awesome trick to combine. Thanks sir

@chandoo_ 2 года назад

Most welcome

@Vaneet.singh13 Год назад

Thank you

@vargheseantony5677 2 года назад

Thank You ❤️

@chandoo_ 2 года назад

You’re welcome 😊

@surajbhardwaj428 2 года назад

Thank you So much Sir....🙏🙏.. Power Query really So Powerful.. I liked your DAX video also.. in this video teached how to combine two table...😊

@chandoo_ 2 года назад

So nice of you

@taizoondean689 2 года назад

Thank you sir 🙏

@chandoo_ 2 года назад

You are always welcome TD :)

@mrsalat1991 2 года назад

Realy greta...tool to learn

@chandoo_ 2 года назад

Thank you Milan.

@t2udu 2 года назад

In an ideal world I'd use SQL or Python or Power Query in that order. But I'm guessing in the real world formulas would be your available option, and with a little copy and pasting it's actually less tedious than I imagined. Great video as usual.

@chandoo_ 2 года назад

Great points. For me the order of priority is SQL › Power Query › Formulas › Copy Paste

@t2udu 2 года назад

@@chandoo_ lol I like that copy and paste gets an honorable mention.

@andrewdove342 11 месяцев назад

Hi CHandoo, after you merged the tables in Power Query, and then "load to" and create connection only, I noticed that you didnt select "add to data model", would you need to add to the data model only if you want to use power pivot and create pivot tables from the new merged data?

@mariasaba8091 Год назад

Hi Chandoo I have a question. We have an excel sheet that contains several tables. Each table represents a day of the month, and the content of all the tables is the same. Is there a way to combine all the tables to one?

@readerisleader12 Год назад

I wasted 3 hours just to understand Xlookup formula 😂 Finally I got a video

@tomotobe 11 месяцев назад

i usually just choose close and load to onto a new worksheet? may i ask what is the different from you making a connecction only and then extending the result table onto a new worksheet?

@sachkhandsingh3157 2 года назад

Hi Chandoo, my name is Harvinder. I have been following you recently and am impressed with the way you teach excel in your videos. I need you help on something. I have a master file and few other files like qualit, attendance and compliance. I want to know how to formulate the sheets so that the master file gets updates when I update any data on the other files. Hope u read my message and reply. Thank you

@armanansari3911 2 года назад

👏👏

@canirmalchoudhary8173 2 года назад

Simple but required many times

@chandoo_ 2 года назад

YES

@Griffindor21 Год назад

Great video! Can this be done in Power BI?

@chandoo_ Год назад

You can do the PQ part in Power BI too.

@jubayerahmed337 2 года назад

❤️❤️❤️❤️

@prem1prakash 2 года назад

Hi Chandoo, as always super. But what if the data is on different workbooks ? Is there a way to merge them using power query merge option without bringing them into the same workbook on different sheets ? Cheers Prem

@raaz845 2 года назад

Absolutely you can do it....simply go to power query there you will see source file tab....from there you load your sheet and then use close and load .....

@vishalbhati912 2 года назад

Yes u can

@gjena1000 2 года назад

@@raaz845 I can’t find source file tab in excel 365 in my work laptop. Do you know what are the other options to get data from separate sheet form the same excel workbook or from different excel sheet

@chandoo_ 2 года назад

You can also use the idea discussed in this video. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YnjcXFjtpa0.html

@Bhavik_Khatri 2 года назад

PQ is the future.

@miletinic920 2 года назад

Nice, can you make advanced video for powerQuery?

@chandoo_ 2 года назад

Sure. I have many advanced videos on it. See the playlist - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-PiFAa_jjaEI.html

@sajjar6925 Год назад

How can we check the differences in two Excel sheets? And how can we find the duplicate data in excel?

@ndumisoradebe5256 10 месяцев назад

Is the power query method still working now. I followed your steps but it doesn't seem to be working for me. In your document, when combining the sales table and people table, the 2 tables only had the first column as a match but when I do the same with my document it doesn't want to merge because the 2nd column in my 1st table is not there on the 2nd table. 1st columns do match. I'm using office 365

@RC-oe6wu 2 года назад

In case the tables have columns calculated with formulas. Is it possible with Power Query to keep the formulas while combining?

@shrirajdeshpande8125 2 года назад

Yes it can be ... Just don't change the column name where the formulas are derived from.

@RC-oe6wu 2 года назад

@@shrirajdeshpande8125 it's not a solution

@chandoo_ 2 года назад

You can keep the formulas in your original data. Upon refresh, PQ will fetch updated values. If you want formulas in final data, then wait until you do your PQ stuff and then write the formulas.

@RC-oe6wu 2 года назад

@@chandoo_ as you say, I need formulas in final data. Basically I receive several tables containing formulas with the same fields and I have to merge them. As you suggest, I know I can write formulas after PQ does its job, but it's not a clever solution for me as I have to do it manually, every month. I am trying to automatise this process, but I understand there is no way that PQ can keep the formulas in the starting tables

@sallyyasayan7535 11 месяцев назад

Is it possible to shuffle one column so it matches the first with the corresponding data. Leaving rows which do not align blank.

@chandoo_ 11 месяцев назад

Hmm.. that is what I am doing in the video.

@TheJoshtheboss Год назад

But I don't want a new merged table. In my instance, I need Power Query to add the looked up data to the original column.

@omkarraut9318 6 месяцев назад

i am having 2 tables in excel & i want to arrange the table 2 as per the sequence of table 1 how do i that ?

@venkatesanmudaliyar4361 Год назад

Hi, Need your help on powerquery, as i have two power querry excel sheet, can i vlookup it from each other

@StefanoVerugi Год назад

if you can load them in Power Query they can be joined based on a common column with Merge

@iamTrillion 2 года назад

Sorry for my ignorance but does it merge/ignore duplicates, eg i worked on a few policy no's yday and have updated the status as completed, pending etc, when i pull a fresh report today and merge it with ydays report it should only show 1 entry of the policy no. that is still pending and not 2 entries. Hope i made sense, any help wud be much appreciated

@chandoo_ 2 года назад

You need to add "remove duplicates" step if you want to skip duplicate entries.

@prachidhaka1189 Месяц назад

i dont get the access of the option close and load to what was the reason

@jeekakrishna 2 года назад

Create streamlit dashboards / PowerBI dashboards videos more

@promiseuzondu8735 Год назад

Hi Chandoo. I have about 62 excel files from 6 health facilities. There is a column that contains patient ID. The problem is that content of this column ranges from numeric to alpha-numeric. When I load the file in power query, it converts numbers like "00002345" to 2345 which defeats my intention. Kindly help.

@emmah3072 Год назад

Excel automatically changes numbers that begin with 0, you have to put a single quote (') before the first 0, or change the entire column to be text instead of numeric