

In the drop-down, click on ‘Combine Queries.In the Get & Transform Data group, click on ‘Get Data’.Here are the steps to merge these tables: The process of merging the third table with the resultant table (that we got by merging Table 1 and Table 2) is exactly the same. You can rename this connection if you want. This will save the newly merged data as a connection. Here are the steps to save this merged table (with data from Sales_Data and Pdt_Id table) as a connection: You need to save this resulting table as a connection (so that we can use it to merge it with Table 3). Now if you only want to combine two tables, you can load this Excel you’re done.īut we have three tables to merge, so there is more work to be done. This would give you the resulting table that has every record from Sales_Data table and an additional column that has product ids as well (from the Pdt_Id table). Uncheck the option ‘Use original column name as prefix’.This is because we already have the product name column in the existing table, and we only want the product ID for each product.

EXCEL QUERIES AND CONNECTIONS TUTORIAL HOW TO
Now let’s see how to merge the Sales_Data and Pdt_Id table. So when you’re done, you will have three connections (with the name Sales_Data, Pdt_Id, and Region). Repeat the above steps for Table 2 and Table 3.

The above steps would create a connection with the name Sales_Data (or any name that you have given to the Excel Table).
EXCEL QUERIES AND CONNECTIONS TUTORIAL CODE
Or if you’re a VBA whiz, you can write a code to do this.īut these options are time-consuming and complicated as compared with Power Query. Now you can rely on VLOOKUP or INDEX/MATCH to do this. You’ll have to map the relevant records from Table 1 with data from Table 2 and 3. To get all this information into a single table, you will have to merge these three tables so that you can then create a Pivot Table and analyze it, or use it for other reporting/dashboarding purposes.Īnd by merging, I don’t mean a simple copy paste. This information is provided as separate tables as shown below: This table has the data I want to use, but it’s still missing two important columns – the ‘Product Id’ and the ‘Region’ where the sales rep operates. In case you prefer reading the text over watching a video, below are the written instructions. One of the things where Power Query can save you a lot of time is when you have to merge tables with different sizes and columns based on a matching column.īelow is a video where I show exactly how to merge tables in Excel using Power Query. With Power Query, working with data dispersed across worksheets or even workbooks has become easier.
