It's the time of year when you start to think deeply about suitable gifts for the spreadsheet users in your life. What better than a gift that has the potential to deliver lifelong spreadsheet time-savings? In the build up to Christmas we will be posting 15 brief, practical guides to ways in which the Excel Power Query feature can automate the manual and solve the insoluble. Most of the posts will be hosted on the Excel Community but we will include a few here as well. The posts will concentrate on Power Query as an additional spreadsheet tool rather than as a data tool.
We will start with a seemingly simple Excel problem that can be very hard to deal with fully automatically using cell-based legacy Excel. We want to combine two (or more) Excel Tables into a single Table that we can base a PivotTable or a chart on, for example.
Here we have some sales order information, with the orders taken by each of three of our sales team on a different sheet. Our task is to combine these three Tables into a single Table and to allow for changes and additions to the three individual Tables to be incorporated into a Summary Table as easily as possible.
We click in each of our Tables in turn and use Data Ribbon tab, Get & Transform Data group, From Table/Range. This opens our Table in the Power Query editor. We just need to use Home, Close & Load dropdown, Close & Load to…, Only Create Connection, OK.
Having added each of our 3 Tables as 'Connection only' queries, we select cell A1 in our empty Summary worksheet, then click on Get Data, Combine Queries, Append. We then choose the 'Three or more tables' option and use the Add button to add each of our Tables to the 'Tables to append' list:
When we click OK, our three Tables are combined into a single Table in the Power Query Editor. Again we use Close & Load To… but this time choose Table, Existing worksheet:
This creates a Table combining the contents of each of our three tables. If we change any of our order Tables, or add or remove rows, we can reflect the changes in our summary Table just by right-clicking in the Table and choosing Refresh.
Next (Excel Community and Tech Faculty only)
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.