+ Reply to Thread
Results 1 to 3 of 3

How to create a link between 2 tables in PowerPivot Diagram with no unique items

  1. #1
    Registered User
    Join Date
    03-23-2018
    Location
    Beirut, Lebanon
    MS-Off Ver
    2013
    Posts
    7

    How to create a link between 2 tables in PowerPivot Diagram with no unique items

    I need to create a link between 2 tables in PowerPivot but my problem is that both tables don't have a unique Item.

    The MainTable contains data that I cannot change since I receive them as they are, it contains transactions for specific items on specific dates and with other criteria involved, so the same item can be repeated several times even on the same date based on the criteria involved.

    The Second table describes the status of this Item at a specific quarter period of the year, so the same item can have a different status during different quarters of the year (Note: only 2 status are present A & B and not all items have a status, so some items can have no status and therefore not mentioned in the second table)

    I need to link them in such a way that I can figure out what was generated by items that had a specific status at a certain period.
    For example: What was generated by items that had status A during the period Q2

    I created a sample file to explain my problem, in it there is a third sheet to make the link between the quarters and the dates (I'm not sure if it is needed, but I don't know how to link them otherwise)

    I can redesign the second table anyway that can solve the problem, even divide it to several tables if that helps
    P.S.: I tried to create 4 tables for the status table, one for each quarter, but then when I link them the pivot only reads the status from the first table and disregards the others...

    Help pleaseeee...
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: How to create a link between 2 tables in PowerPivot Diagram with no unique items

    Try to use Power Query to connect "MainTable" and "Status".
    Add the combined table to the data model and connect to "Date"
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-23-2018
    Location
    Beirut, Lebanon
    MS-Off Ver
    2013
    Posts
    7

    Re: How to create a link between 2 tables in PowerPivot Diagram with no unique items

    OMG stasinek, it works

    I used the merge function in PowerQuery and it worked beautifully

    this was very helpful thx a lot.

    this case is Solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. PowerPivot lookup table to create link between tables
    By Harveyhirst1 in forum Excel General
    Replies: 0
    Last Post: 10-09-2018, 01:02 PM
  2. Create a unique list of items from two tables
    By XLalbania in forum Excel General
    Replies: 22
    Last Post: 01-15-2017, 06:16 PM
  3. Replies: 1
    Last Post: 08-23-2016, 05:06 AM
  4. Replies: 0
    Last Post: 07-08-2016, 12:20 PM
  5. [SOLVED] Seeing diagram relationships in powerpivot
    By stephme55 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-05-2016, 03:06 PM
  6. Create relation between 2 PowerPivot tables - not working - why?
    By oleisbest in forum PowerPoint Formatting & General
    Replies: 1
    Last Post: 09-04-2014, 10:16 AM
  7. Create relationships in PowerPivot using two unique fields
    By Algonquin Dude in forum Excel General
    Replies: 0
    Last Post: 12-01-2011, 09:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1