0

I am trying to learn with PowerPivot and from what I saw, it would be THE tool to help me reconcile similar data in two different systems. However I cannot seem to get through the basics - I have extracts from two systems and they both share the same ID key in a format similar to below

ID Name Other_things

1 Eleshar ...

2 Vermillion ...

The ID is unique in each system (no duplicates) and it is exactly the same format and data type. However when in the data model I establish relationship between them and try to put together ID from table1 and e.g. name from table2, the PowerPivot will get me all names from the table2 for every individual ID from table1.

As thing of note: the relationship between the two tables in the data model shows as one to many and I have not figured out a way to change it to many to one. I have also tried to switch the direction of the relationship but the result is the same.

What I am trying to achieve is basically a comparison table showing ID Name_Table1 Name_Table2 (etc. for other values - I know I could try using VLOOKUP or INDEX&MATCH but I have about 100 columns to compare and PowerPivot was advertised to me like a good idea for solution of this).

Improve this question
1
  • Power Query (aka Data ribbon, Get & Transform data) would probably be more useful for this task.
    – Mike Honey
    Nov 12, 2018 at 0:29

1 Answer 1

Reset to default
2

I have just figured it out - the solution made me facepalm.

It all sorts itself out when something is added into the Values section of the pivot table. Before, I was just adding rows first.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .