locked
Merging two tables with relations RRS feed

  • Question

  • I've got the following problem:

    I import two tables in PowerPivot. The first table called "list of products" contains the rows "supplier_id" and "product-category". My second table called "Suppliers" contains the "supplierName" and "supplier_id", as well.

    Now, I add a relation between the supplier_id of the "Supplier" table (unique) and the supplier_id of the "list of products" table.

    My goal is to create a (Pivot)Table which lists the supplierNames and the product-categories. E.g. Microsoft (supplierName) prduces  Office Software and Operating Systems (product-categories).

    I wasn't able to find a solution even after three hours of trying every button of Excel2010 and PowerPivot.

    I would be really grateful if somebody could help me.

     

    Best

    Felix

    • Edited by Kiechlus Tuesday, June 8, 2010 6:21 PM
    Monday, June 7, 2010 10:35 PM

Answers

All replies

  • if you have issue in finding where to create relationship then it is quite easy . go to powerpivot button in your excel . click on that . it will open 

    you can see 2 tabs in your window. Home and Design.

    Go to design tab and click on create relationship. There you can create relationship. Then save and come out to main excel and click on Pivot Table and you can see the tables. 

    Also I recommend you watch this video series . http://channel9.msdn.com/posts/MSCOMSWE/PowerPivot-Deep-Dive-with-Rob-Collie-14/

    Hope this helps


    Ashwani Roy

    My BI Blog

    My Entity Framework Blog

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, June 8, 2010 3:28 PM

  • Hi Ashwani,

    thanks a lot for your answer but unfortunately that did not help. I was able to figure out the location of the relationship button and I already created the neccessary relations.

    It still isn't a 100% clear to me what they a good for, though. What I want to do is to connect the two tables and "merge" them in some kind of way. The "supplier_id" appears in both tables. As I have written above, the the table with information about the suppliers lists all the suppliers with their ID, company name etc.

    Now, what I call merging comes into play. Another table ("list of products") contains information about products but does not contain the real name of the suppliers but just their ID. My aim is to create a new table (now in real excel) with the information about the products and the REAL name of the supplier instead of the ID.

    Example :

    Table 1 ("Suppliers"):   

    ID: 12  ---  company name: Microsoft --- Address ... ...

    ID: 14 ---  company name: Google--- Address ... ...

    Table 2 ("list of products") 

    Product : Windows 2007 --- Category : Operating System --- SupplierID : 12

    Product : Gmail --- Category : Mail client --- SupplierID : 14

    --> Merged table

    Product : Windows 2007 --- Category : Operating System --- Supplier: Microsoft

    Product : Gmail --- Category : Mail client --- Supplier: Google

     

    Am I on the right path? Does Excel / PowerPivot provide this kind of functionality at all?

     

    Best

    Felix

     

    Tuesday, June 8, 2010 4:21 PM
  • Hi Felix,

    Based on the description, it sounds like your best approach would be to do one of the following:

    1. Create a view in the relational source, something along the lines of:

    SELECT SUPPLIER.SUPPLIERID, SUPPLIER.SUPPLIERNAME, PRODUCT.PRODUCTCATEGORY FROM PRODUCT INNER JOIN SUPPLIER ON SUPPLIER.SUPPLIERID=PRODUCT.SUPPLIERID

    then import the view into the PowerPivot table.

    2. Use a custom query similar to the one above as the source for the PowerPivot table.

    Regards,

    John Desch

    Tuesday, June 8, 2010 10:53 PM
  • ooh i get it . You wanna join in Power pivot itself using some kind of programming.

    FROM http://social.msdn.microsoft.com/Forums/en/sqlkjpowerpivotforexcel/thread/dfed10a7-2666-4f88-a973-9d8aa319dec0

    Unfortunately, there isn't a way to have multiple data sources import to a single table in PowerPivot with this version.  If the data is stable and won't need frequent refreshes, then you could use cut and paste to create a new PowerPivot table for the first spreadsheet and then cut and paste to append to the new table from the other spreadsheets.  Otherwise, you will need some intermediate data manipulation tool that can join the data from the spreadsheets together so they appear to be a single data source to PowerPivot.  

    The ability to append to an existing table during data import is a feature that will be considered for the next major release of PowerPivot, it just didn't make it into the current product cycle.

    So you can copy the 2 sets and append it by pasting , write a middle layer (Macro ) to join the 2 data sets or just join it in the source (INNER JOIN ) as suggested in the reply below. 


    Ashwani Roy

    My BI Blog

    My Entity Framework Blog

    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, June 8, 2010 11:12 PM
  • Hi John and Ashwani,

     

    Thanks again for your replies. I'm sorry to ask, but how do I create a view and what is it? Is this somehow SQL related?

    Because SQL is not where we are headed (even if it is the name of this forum, but I did not find another one). I wanted to test the behaviour I described (merging two tables) at first with the Northwind database [1], which is available in Odata and which I loaded into PowerPivot via AtomFeed.

    Later, our aim is to convert RDF to Odata and use Excel to visualize and manipulate data from the huge amounts of RDF already available on the net or available soon. Merging tables would be one basic manipulation operations we'd need.

    Thus, neither SQL queries nor copy and paste help us along. The Macro thing sounds more promising but first I need to be 100 % sure that there's no way to do this with native Excel/PowerPivot.

    Another thing: Do you know if there'll be RDF / Sparql support for Excel 2010 / 20XX? Or has anybody heard of already existing wrappers which convert RDF triples to Odata?

     

    Best

    Felix

     

    [1] http://services.odata.org/Northwind/Northwind.svc/

    Friday, June 11, 2010 9:48 AM
  • The only 2 ways I can see are

    1. you join the data before loading it to Power Pivot. As join mentioned in SQL View or some intermediate layer where you programatially join the data tables in C# or something

    2. Join using Marco after it is loaded.

    I am not aware of RDF support in Excel 2010 but there must be extensions that you can download which can convert this to tables provided by some third parties.

    This is turning out to be interesting so I would be very much keen to see what MSFT has to say on this .


    Ashwani Roy

    My BI Blog

    My Entity Framework Blog

    Please click the Mark as Answer button if a post solves your problem!

    Friday, June 11, 2010 10:45 AM
  •  

    Does anybody know something on this? Would be really helpful because we need to know if we can use PowerPivot on our purpose and hava a presentation soon. 

    Best

    Felix

    Monday, June 14, 2010 2:38 PM
  • Hi Felix,

    Yes, a view is something that would be done in the context of a relational database as a data source. Since you're pulling data from an Atom data feed, you aren't going to be able to merge data from two different Atom Feeds during the import.

    Assuming that there is a common field between two data sets from an Atom feed, you could establish a relationship between the two imported tables, which would allow you to establish an indirect relationship to a third imported table.

    HTH

    John Desch

    Monday, June 14, 2010 11:14 PM
  • Hi John,

    sorry, I don't understand. What is an indirect relation and how could a third imported table help me with my problem? I'd rather create a new table with the behaviour I described. But perhaps this functionality is just not implemented yet and we are talking at cross purposes.

    Does anybody know something about the RDF/Odata difficulty?

    Best

    Felix

    Tuesday, June 15, 2010 8:50 PM
  • I did a blog post on merging two tables with different layouts but you can use the adivce if the layouts are the same as well.   You can find it here:

    http://alanwhitehouse.wordpress.com/2011/03/24/merging-tables-with-different-columns-and-layouts-in-powerpivot/

     

    Alan


    My Ramblings @ http://alanwhitehouse.wordpress.com My Tweets @ http://www.twitter.com/alanwhitehouse
    Tuesday, May 10, 2011 5:25 PM