How to combine tables with different columns in excel

How to combine tables when the columns are in different places in each table and not all the columns match up.

In this video I have 3 tables one for each year from 2013 to 2015 and in each table are concentrations of a range of compounds from air samples collected in each year. However not all the same compounds were measured in each year. Some compounds we measured in just one or two years. Because of this the compound measurements for each year are not always in the same column and therefore I cannot just copy and paste the tables underneath each other. In order to combine the tables I use a query (also called power query or get and transform). Using a query I can append a table onto another table and the columns will automatically match up so long as they have the same column headings. This is much quicker than manually moving and matching up the columns if you have a lot of columns.
---------------------------------------------------------------------------------
#exceltutorial #excelpowerquery #exceltipsandtricks

VIDEO INFO: These data are related to my PhD research
---------------------------------------------------------------------------------
IF YOU LIKE THIS VIDEO YOU MAY ALSO LIKE:

[stephennaylor]

Great thanks very much. Pretty much wasted an entire afternoon trying to follow half hour long Power Query videos and then you explained it perfectly in 4 mins. Are you single?

[TrepWorldwide]

Thanks for this. Wonderful video that has saved us countless hours putting 20+ sheets with varying (but similar) numbers of unique and shared columns. Worked like a charm.

[vivekthaker]

Bossed it. Thanks Karina, this saved time that would have been spent writing up VBA code. Really clear and a great example of what Power Query can do.

[aredhelwinter]

Hi! Thanks for showing us how to do this. It helps a lot. My only concern with this is that I have more than 10 files that I need to convert to tables. Is there a way other than opening each file and converting the sheets into tables to make sure the headings align?

[PatriotStud]

Thank you! Does this work if you do not first create the connections? For example, if you just went straight to the Query Editor and "Appended 3 or more sheets"? I'm not understanding the purpose of creating the Connections in the first step.

[yassineerrachid]

Thanks a lot. What f the table that I want append contains some rows that are similar in the first column to the initial table but with more data in two other columns that also exist in the initial table ?

[BasilHuzaifa]

Thank you, is there a way to combine two tables, but they have different columns. they have a primary key.
I need to create a connection with the primary key between the two tables, then combine the two tables together without using a pivot table

[needbasedinfrastructuredev]

i could not find get & transform option in my excel 2010. please help

[mynksha]

I have 2 excel sheet, one having student name, class & roll num and another sheet having student name & class. Now in sheet 2 we want a formula, if student name & class is matched, who auto pick student roll num. Pls advice.

[casst]

almost impossible to hear this video..

[gekarekides]

Love you. I would propose to you if I were handsome