Combine Excel Worksheets in Power Query

in this tutorial we're going to look at how we can combine the budget data on this sheet with the actual data that we have on this sheet now my case there on separate worksheets but for you they might be in different workbooks or even different systems wherever they are we need to combine them so that we can then do our variance analysis normally we'd use multi column vlookup or index and match formulas to bring these actuals into a column on the budget table and let's just take a look at how we do that we'll lose index now we wanted a reference table one which is the table containing my actuals so we want to look up the actual sales figure and we use match to find the row so we want to match the month and the category and the product with the corresponding columns on the actual table so the month and table ones category column and table ones product column and we want to match them exactly so wouldn't 0 in there close our parentheses on that and close it on index press control shift and enter because this is an array formula and you can see it's brought in the figures let's just give this a name actual sales so it all looks good except this row here we obviously haven't got an actual for beverages in January for the products chamomile because it's returned and then I can't find a value there so the other problem that we could potentially have is we don't know if it's brought in all the actuals is only going to bring in actuals where we had a budget so there could be instances where we don't have a budget and let's just check that I'll delete the error so I can have a look so we've got here let's put a comma separator in we've got here one point nine nine five million you know actual sales table we've got 10.99 7 million so there's obviously more actuals than we have budget line items for and so the process is to find which actual sales are missing and it's as back and forth thing but instead of using formulas we can use power query so let's take a look I'm going to delete that column and we're going to use power query to do this the first thing we need to do is to load these two tables into power query now on the power query tab you'll see there's lots of different sources we can use to get data mine is housed in an Excel table so I'm just going to click on the from table and that opens the query editor window first thing I'm going to do is give it a name so it's easy to identify so I'll call this actuals and I'm just going to close and load - I'm not going to do any more there it's going to create a connection to this table now click load you can see now I have a connection to my actuals table here let's repeat that for the budget so from table we call this budget and again close and load - and I just want a connection and then load so now I have my two connections to the actuals and budget and I want to combine those two tables together and to do that on the power query tab I'm going to use append so we'll select the first table which is the actuals and then the budget so it's going to append these two tables together and this again is the query editor where we've got a new query we'll call this budget and actuals now so far we haven't done any more than really copying and pasting the budget figures underneath the actual figures you can see we've got a column for budget sales and we've got a load of nulls and that's because what we're seeing here are the actual sales figures first and then down below if I were to scroll down far enough we'd see the budget figures but power query only shows me a preview so I can see there's more than 999 rows and it's just showing me a preview ideally what I'd like to do is have my budget sales on the same row as my actual sales where there's a line item for it so I want to find January's averages English breakfast budget and I can do that empower query with the group by tool so I'll just have to save my name the first thing I want to do is to select the columns that I want to group by and then click on group by it's on the Home tab and will automatically populate those three columns in my group by list I can add more or remove these using the plus and minus buttons here but they're correct that's what I want and then these are going to be the columns for my actual so let's give it a name actuals and I want to sum the actual sales column and then I want one more for my budget and again I want some the budget sales so this will group my data by these three columns and I'll give me two new columns called actuals and budgets so now you can see I only have 859 roads its consolidated it down where it can you can see here for the black tea we don't have a budget but we have an actual and that would have been one of the line items that contributed to the difference that I had when I used index and match okay now I ready to load it back into Excel first of all I just want to format the date and time I don't need the time it's all just twelve o'clock so I'm just going to set it to date I can also set the format for my number columns that you can see they're already formatted as decimal number and my category and product are text so I don't need to do any more I can close in load and I can either load it into a table or I can create a connection if I load it into a table I can choose where to put it but I can also add it to the data model and that's power pivot so I'm using Excel 2013 and when you have a direct route from power query into power pivot with this check box I'm going to just put it in a new table on a new worksheet so I'll click load so there's my new consolidated data I'm just going to keep this sheet a name this is my query output and you can see now in my queries I've got a three got my original connections to actuals and budget and I've got my consolidated or combined data if I scan to the very bottom you can see I've got budget figures for November and December but I don't have any actuals then this is typical scenario because you're going to get your data every month maybe even every week and you want to add to that each time you get new data and it's easy with power query so here I've got my actuals for November and December I'm just going to copy them and then I'm going to go to my actual table and I'm going to paste them at the bottom you can see my tables grown now it now goes down to row 859 I can go back to my query and if I right click and refresh power query goes and gets the data runs it through the cleaning processes that I applied which was to remove the time from the date and it's popped it into my query and I haven't had to do any more and that's one of the amazing things about power once you set up the steps I'll show you in the query once you set up these steps they can be used again and again a bit like you might record a macro and like I said we don't have to always bring data in from an Excel table if we look on the power query tab we can get data from all these different sources if you want to download the workbook for this video click this link to visit my blog where you'll find the workbook download thanks for watching

Channel: MyOnlineTrainingHub

Views: 40,841

Rating: 4.9549112 out of 5

Keywords: excel, power query, excel power query, combine data with power query, Microsoft Excel (Software)

Id: wOVMJXfnQHk

Length: 8min 1sec (481 seconds)

Published: Mon Oct 19 2015