Have you been experimenting with the Power BI tools that are available in the newer versions of Excel. I’ve done some work with Power Pivot, and was impressed by what could be done with that add-in. But, despite its capabilities, I haven’t been using it for big projects, or client work.
Recently though, I’ve been testing Power Query, and the things that it can do are very exciting. I’m just getting started with this add-in, but was amazed by how easy it is to combine data on two different worksheets.
Once the data is combined, you can filter and sort it, all in one place. Or, create a pivot table from the combined data. It’s much easier, and with better results, than the old methods of working with Multiple Consolidation Ranges.
Add Tables With Power Query
You can download the Power Query add-in from the Microsoft website, as long as you have one of the versions of Excel that supports it.
I had two sheets – East and West – with a named Excel table on each sheet. I used Power Query to add each table, with just a couple of clicks.
As each table was added, a query was automatically created, and listed in the Queries pane, with a Refresh button for each query.
Combine the Tables
The next step was easy too – click the Append button, and select a table from a drop down list.
The combined data shows all the columns from both tables. Each table had one heading that wasn’t in the other table, but that didn’t cause any problems.
Create a Pivot Table
After the data was combined, I created a pivot table from the data, and if either of the original tables is updated, a Refresh All (or two) will update the combined table and the pivot table.
Video: Combine Data From Two Worksheets
Watch this video to see how to combine the data from two similar tables, create a pivot table, and refresh the data.