Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Create a calculated table from two or more existing tables

I have serial numbers and locations in two different tables. I want to compare the datasets by putting the data side-by-side to show for each serial number, which location each dataset shows for each serial number.

 

Like this:

Serial NumberDataset 1Dataset 2
1234Location 1Location 1
5678Location 2Location 5

 

 

What I'd like to do is create a calculated table that unions the serial numbers from both datasets and removes duplicates.  Then I can join that table to both datasets and create a grid that returns all serial numbers from the calculated table with the first location from Dataset 1 and the first location from Dataset 2.

 

But my formula isn't getting there:

 

Serial Numbers = DISTINCT(SUMMARIZE('Dataset 1','Dataset 1'[Serial #]) & SUMMARIZE('Dataset 2','Dataset 2'[Serial #]))

Any ideas? Or is there a better way to get the result I'm going for? (A couple of complications... there are instances of duplicated serial numbers in both datasets, and both datasets have serial numbers that aren't in the other dataset.)

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Actually I found that what I was missing was the UNION function. Creating this calculated table gave me just what I needed:

     

    Serial Numbers = DISTINCT(
    UNION(
        SUMMARIZE(
            'Dataset 1',
            'Dataset 1'[Serial #],
            ),
        SUMMARIZE(
            'Dataset 2',
            'Dataset 2'[Serial #],
            )
        )
    )

    Then I was able to join this calculated table to each of the original tables and pull in location, using IFERROR to handle cases with more than one match:

     

    Dateset 1 Location = IFERROR(
    LOOKUPVALUE(
        'Dataset 1'[Location],
        'Dataset 1'[Serial #],
        'Serial Numbers'[Serial #]
        ),
    "Multiple"
    )

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Within the query editor you can merge queries as new using an inner join.

  • erik_tarnvik's avatar
    erik_tarnvik
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    Hi Anonymous,

    I agree with Anonymous, this is easier to accomplish in Power Query. If you don't want to go that route, I answered a similar question just an hour or so ago. I am not completely satisfied that I arrived at the most elegant way to accomplish this but in case you want to have a look, check out my response at the end of this thread.

     

    http://community.powerbi.com/t5/Desktop/Connecting-Two-Tables-w-Lookup/td-p/276284/page/2

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Actually I found that what I was missing was the UNION function. Creating this calculated table gave me just what I needed:

     

    Serial Numbers = DISTINCT(
    UNION(
        SUMMARIZE(
            'Dataset 1',
            'Dataset 1'[Serial #],
            ),
        SUMMARIZE(
            'Dataset 2',
            'Dataset 2'[Serial #],
            )
        )
    )

    Then I was able to join this calculated table to each of the original tables and pull in location, using IFERROR to handle cases with more than one match:

     

    Dateset 1 Location = IFERROR(
    LOOKUPVALUE(
        'Dataset 1'[Location],
        'Dataset 1'[Serial #],
        'Serial Numbers'[Serial #]
        ),
    "Multiple"
    )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    great solution...and quick too.