How to Join Tables in Excel | Merge or Append Data From Different Sheets Using Power Query

okay so you want to join tables together in microsoft excel two possible things that you'd want to do you either want to append data or merge data so i'll give you an example of both appending data is where you want to stack rows on top of each other from different tables for example i've got three statements here and what i want to do is combine all the statements in one table so that's a pending data the other example is where you want to merge data this is where you want to combine columns from different related tables so for example here i've got three tables i've got a transaction table a product table and a sales rep table and these are all related tables so i want to get all the columns from these three tables combined into one table so pending is where you're stacking rows on top of each other and merging is where you're combining all the columns from different tables together let's start off with appending now the first thing you want to do is house your data within excel tables first step is to select the data now with my data i've got blank rows and columns which is fine but that does mean i need to manually select the data if you don't have blank rows and columns in your data you can just click in a cell with data in it and that will suffice for me i need to select all of the data and then i'm going to go to the insert tab on my ribbon and click on table now you need to be mindful of this setting here my table has headers if your column headings are in the first row of your data then you need to make sure this is ticked for my data that's not the case the column headings are actually in row four so i need to leave that unticked so i'll click on ok and then on this table design tab i'd give the table a name so i'm just going to call this july because it's the july statement press enter to confirm so i need to do the same thing for the other tables i select the data there is a shortcut key for insert tables control t and then click on ok give the table a name and i'll do the same for september now once the data is in tables what you do to go to the data tab on your ribbon you go to get data from other sources blank query in the formula bar you type the following formula equals excel dot current workbook you need an open and close bracket press enter and that'll list all of the worksheets in your workbook next you just click on this little expand button here click on ok and that will append all of the data into one table now that may be enough for your particular data but for mine there are some transformations i need to make for example i have this additional information at the top of each statement which i need to get rid of you can see that repeats for each statement i also need to get rid of these blank columns now to get rid of the first three rows the top of the statement go to remove rows remove top rows and then i type in the number of rows i want to delete now i want this row to become the header for my columns so i've got a button up here use first row as headers and then to get rid of this blank row here so remove row remove top row number of rows one now you'll notice down the side here is actually listing all the steps in my query editor so i can always delete a step if i've got it wrong or i can change a step by clicking on this little settings button that's pretty good for the first statement but you'll notice that those three rows still appear at the top of the other statements now how do i get rid of those well first step would be to change the data type for this column this column contains date so i'm going to change the data type to date and that causes an error wherever there isn't a date in that column so what i can do is now remove errors and then i can filter out the null values next i'm going to get rid of these columns and i can do things like apply currency format to these other columns and i could get rid of this column altogether and then i'd probably give the query a name so i'll call this all statements and then i'm ready to just load it into excel so i click on this button up here top left so it's created a new sheet called all statements and if i look through this it's combined or appended all of the statements together in one sheet okay so that's my appending example hopefully that's useful now let's move on to the merging example so here i've got three sheets and i want to combine the columns together into one table now i've already put these data sets in tables so the first one is called f transactions this one's called d products and this one's called employee so the next step is to create a query for each of these tables so i go to data i'll click on this button from table range and then i'll go to close and load close a load two and only create connection you'll see over here in my queries at connection pane i have my first query then i go to the next table and i do the same thing click somewhere in the table from table range close and load close and load two only create connection so again i've got another query over here in the queries connections pane so i've just got to do the same for the third table only create connection so i've got my three queries now for the three tables in my data so i'm going to start by right clicking on the transactions query and i'm going to go to merge and what i want to do is i want to merge this table with the products table now the column that links these two tables is the product id column so i'm going to select the product id column in the transactions table and also select the product id column in the products table so i'm now going to click on ok now you'll see over here i've got a d products column what i'm going to do is click on this expand button and i've got to select which columns within the products table i want to merge with the transactions table so i want brand description and unit price and i'm going to untick this option use original column name as prefix so you can see now it's nicely merged those three columns with the transaction data so now i want to merge this data with the employee table so i go up to merge queries here click on the little drop down and i'm going to click on merge queries i'm going to select the employee table and this time i'm going to select the sales rep id column in both of these tables click on ok and then i'm going to expand the employee column and select which columns i want to merge with this data so i want name position and salary click on okay so now i've merged the columns from all three tables now there may be some transformations i want to do for example over here you can see these dates are in the form year month and day it'd be nice to show them as proper dates at the moment they're just stored as numeric values so if i go to this button here and the first thing i do is i change the column to a text column and then i change it to date and you always add a new step if you're going to do this now the proper dates so over in the unit price column i might change this to currency and also the salary i think i'll change the currency okay i think i'm done i'm going to call this query all data and then i'm going to close and load it to excel click on this button top left so now you can see all the data is merged together in one table okay that's all i wanted to cover in this particular video hopefully that's useful if it is please give me a thumbs up and subscribe and i'll see you next video [Music] you

Channel: Chester Tugwell

Views: 45,276

Keywords: excel merge two tables based on one column, merge tables in excel from different sheets, excel combine two tables power query, how to merge data in excel, join tables excel power query, microsoft query join tables excel, merge queries and join tables excel, left join tables excel, join two tables excel, excel merge tables from different sheets, excel append tables from different sheets, how to append data in excel, append two excel tables into one, append two tables excel

Id: 3vr36gDOLlI

Length: 9min 56sec (596 seconds)

Published: Fri Aug 26 2022