Combine Data on Two Worksheets

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.

combinetable02

As each table was added, a query was automatically created, and listed in the Queries pane, with a Refresh button for each query.

combinetable05

Combine the Tables

The next step was easy too – click the Append button, and select a table from a drop down list.

combinetable06

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.

combinetable08b

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.

combinetable09

Power Query Training

Excel’s new Power Query add-in can do amazing things with your data. If you’d like to learn more about it, experts Ken Puls and Miguel Escobar have launched a new website, where they will offer live, online Power Query training. You’ll have two days of live online training (4 hours per day), then a live follow-up Q&A session, about a week later. Take a look, and if you decide to register, use the coupon code DEBRA to get 10% off.

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.

________________

You may also like...

10 Responses

  1. Ken Puls says:

    Hey Deb,

    I noticed that your dates are showing up in Excel with serial numbers, rather than the date format from your original tables. You can fix this by selecting the date column in Power Query, then choosing Transform –> Data Type –> Date, then loading the query into Excel.

    :)

  2. Mark Blackburn says:

    Hi Debra,

    Thank you for the Video. Quick question, using Power Pivot and Power Query is there a way to create a table in Power Pivot/Query that contains more than 1 million rows? For example, I have 12 Excel files with about 750k rows each for each month of the year. I would like to report on that from 1 source/table. I look forward to your response. Thank you.

    Regards,
    Mark B

  3. ahmed says:

    does power query supports in ms excel 2007?

  4. Dritan says:

    How can I combine/merge two dynamic table in one (the table have the same nr. of columns)?
    I have try but I could not get it?

    Any help, pls.

  5. Peter Griffiths says:

    Hi Debra, I may be wrong but from memory the Power Query add-in only works with 32bit excel whereas for most of my work I use 64bit to get the memory benefits (I use 16gb ram but believe the limit of 32bit excel is 8gb or less. I have tried combining large worksheets of a similar size to Mark Blackburn above where close to the 1m limit of rows but didn’t find the combination / subsequent pivot table operation especially easy or fast and as I say I think I was limited to 32 bit which slows things down further. Anyway, always a good read and I enjoy your blog. Cheers, Peter

  6. Rudra says:

    Thanks Debra,
    Thanks for letting me know something new.

Leave a Reply

Your email address will not be published. Required fields are marked *