Merge tables

[Bilingual]

Hi, i have a very simple question, how do you merge two Excel 2010 tables in separate files with each other? - is there a simple way to do so?

I have tried with UNION ALL SELECT * but i wont connect to the second file even though the path is correct.

They have two variables in common, Month and year.

 

[dhExcel]

The beauty of PowerPivot is that you do not have to merge tables. You just connect to both of them. If your goal really is to merge tables, however , your question then would not be appropriate for this section.

 

[Bilingual]

The beauty of PowerPivot is that you do not have to merge tables. You just connect to both of them. If your goal really is to merge tables, however , your question then would not be appropriate for this section.

<

I am sorry, i am not native english speaking so i might not express myself clearly.

I have tried to read how to create a common pivot table from total data from both tables, but each time i try to connect the two files, instead of the common variables i pivot, i get two set of variables for each dataset.

 

[MD610]

If I am understanding correctly, month and year are the 2 common fields between the tables. I am also assuming that each table has separate fields called month and year.

You can try using =concatenate to create a new column in each table (in the PowerPivot window) and then use this newly created column in each table for your relationship.

For a PowerPivot relationship, one of the tables must contain unique values in the join field, so if this is not the case after you create a new MonthYear field in each table, you could try creating a 3rd table that simply lists unique MonthYears. Then relate both your original tables to this new 3rd table of unique MonthYears.

 

[Bilingual]

Hi again!
I am quite experienced in Excel and Pivot tables, but using Power pivot is for me another planet!
My big hurdle is that i do not know where to start when i want to connect to tables and create a common pivot table.
Lets go back to basic and say that i :

1. Have a single Excel file with two sheets

2. This Excel file has month-year in common in a table and i want to jo these so i can create a common pivot.

Where do i start?

 

[MD610]

There are many good resources on the web as well as various books that can give the the basics of PowerPivot in detail.

In general, you need to make sure you have the free PowerPivot add-in installed and activated. You should see a PowerPivot menu in the ribbon.

Then click on a cell in your first table, go to the PowerPivot menu and select Create Linked Table under Excel Data. This imports the worksheet into PowerPivot and should automatically open the PowerPivot window which is separate from your excel worksheets. Repeat the same process for your second worksheet.

Once both are loaded to PowerPivot, in the PowerPivot window, go to the Design menu in the ribbon. Select Create Relationship and follow the steps to join the tables. It sounds like you will have to follow the steps in my previous post and either concatenate your month and year fields (directly in the PowerPivot window by going to the last column of your tables and adding a new field) or create a third unique table of dates (in excel and then import to PowerPivot like you did with your original tables) so that you have at least one table with a unique join key.

 

[Bilingual]

Thanks i try that, i have also ordered a book which can guide me through powerpivot.